Horton

Re: POC, WIP: OR-clause support for indexes

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

View thread
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: jian he <jian.universality@gmail.com>
Cc: Alena Rybakina <a.rybakina@postgrespro.ru>, Alexander Korotkov <aekorotkov@gmail.com>, Robert Haas <robertmhaas@gmail.com>, pgsql-hackers@postgresql.org, Peter Geoghegan <pg@bowt.ie>, "Finnerty, Jim" <jfinnert@amazon.com>, Marcos Pegoraro <marcos@f10.com.br>, teodor@sigaev.ru, Tomas Vondra <tomas.vondra@enterprisedb.com>, Peter Eisentraut <peter@eisentraut.org>, Ranier Vilela <ranier.vf@gmail.com>
Date: 2024-03-05T05:30:25Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Make group_similar_or_args() reorder clause list as little as possible

  2. Allow usage of match_orclause_to_indexcol() for joins

  3. Skip not SOAP-supported indexes while transforming an OR clause into SAOP

  4. Remove the wrong assertion from match_orclause_to_indexcol()

  5. Teach bitmap path generation about transforming OR-clauses to SAOP's

  6. Transform OR-clauses to SAOP's during index matching

  7. Fix the value of or_to_any_transform_limit in postgresql.conf.sample

  8. Transform OR clauses to ANY expression

  9. MergeAttributes code deduplication

  10. SEARCH and CYCLE clauses

  11. Improve estimation of OR clauses using extended statistics.

  12. Teach btree to handle ScalarArrayOpExpr quals natively.

  13. Revise collation derivation method and expression-tree representation.

  14. Instead of trying to force WHERE clauses into CNF or DNF normal form,

On 4/3/2024 09:26, jian he wrote:
> On Thu, Feb 29, 2024 at 4:59 PM Andrei Lepikhov
>> Feel free to add, change or totally rewrite these changes.
On replacement of static ScalarArrayOpExpr dest with dynamic allocated one:
After discussion [1] I agree with that replacement.

Some style (and language) changes in comments I haven't applied because 
it looks debatable for me.

> I think it should be something like:
> + gettext_noop("Transform a sequence of OR expressions to an array
> expression."),
> + gettext_noop("The planner will replace expression like 'x=c1 OR x=c2 "
> + "to the expression 'x = ANY( ARRAY[c1,c2])''"
Fixed

> queryId may not be a good variable name here?
Not sure. QueryId is a concept, part of queryjumble technique and can be 
used by other tools. It just tells the developer what it is the same 
thing as Query Jumbling but for a separate expression.
At least you don't insist on removing of JumbleState return pointer that 
looks strange for a simple hash ...
> 
> comment `/* Compute query ID */`
> seems not correct, here we are just hashing the expression?
The same as above.
> +/*
> + * Dynahash match function to use in guc_hashtab
> the above comments seem not correct?
Yes, fixed.

> ` It applies to equality expressions only.` seems not correct?
> `select * from tenk1 where unique1 < 1 or unique1 < 2; ` can also do
> the transformation.
Yes, I forgot it.
> `similarity of variable sides.` seems not correct,
> should  it be 'sameness of the variable sides`?
The term 'equivalence' looks better *).

> in [2], we can get:
> SOME is a synonym for ANY. IN is equivalent to = ANY.
> 
> but still transforming OR to ANY is not intuitive.
> a normal user may not know what is "transforming OR to ANY".
> so maybe adding a simple example at
> <varlistentry id="guc-enable-or-transformation"
> xreflabel="enable_or_transformation">
> would be great. which, I did at previous thread.
Not sure. Examples in that section are unusual things. What's more, 
should a user who doesn't know what it means to change this setting? 
Let's wait for other opinions.

[1] https://www.postgresql.org/message-id/2157387.1709068790@sss.pgh.pa.us

-- 
regards,
Andrei Lepikhov
Postgres Professional