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: Alexander Korotkov <aekorotkov@gmail.com>
Cc: jian he <jian.universality@gmail.com>, Alena Rybakina <a.rybakina@postgrespro.ru>, 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-13T05:52:50Z
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,

Attachments

On 12/3/2024 22:20, Alexander Korotkov wrote:
> On Mon, Mar 11, 2024 at 2:43 PM Andrei Lepikhov
>> I think you are right. It is probably a better place than any other to
>> remove duplicates in an array. I just think we should sort and remove
>> duplicates from entry->consts in one pass. Thus, this optimisation
>> should be applied to sortable constants.
> 
> Ok.
New version of the patch set implemented all we have agreed on for now. 
We can return MAX_SAOP_ARRAY_SIZE constraint and Alena's approach to 
duplicates deletion for non-sortable cases at the end.
> 
>> Hmm, we already tried to do it at that point. I vaguely recall some
>> issues caused by this approach. Anyway, it should be done as quickly as
>> possible to increase the effect of the optimization.
> 
> I think there were provided quite strong reasons why this shouldn't be
> implemented at the parse analysis stage [1], [2], [3].  The
> canonicalize_qual() looks quite appropriate place for that since it
> does similar transformations.
Ok. Let's discuss these reasons. In Robert's opinion [1,3], we should do 
the transformation based on the cost model. But in the canonicalize_qual 
routine, we still make the transformation blindly. Moreover, the second 
patch reduces the weight of this reason, doesn't it? Maybe we shouldn't 
think about that as about optimisation but some 'general form of 
expression'?
Peter [2] worries about the possible transformation outcomes at this 
stage. But remember, we already transform clauses like ROW() IN (...) to 
a series of ORs here, so it is not an issue. Am I wrong?
Why did we discard the attempt with canonicalize_qual on the previous 
iteration? - The stage of parsing is much more native for building SAOP 
quals. We can reuse make_scalar_array_op and other stuff, for example.
During the optimisation stage, the only list partitioning machinery 
creates SAOP based on a list of constants. So, in theory, it is possible 
to implement. But do we really need to make the code more complex?
> 
> Links.
> 1. https://www.postgresql.org/message-id/CA%2BTgmoZCgP6FrBQEusn4yaWm02XU8OPeoEMk91q7PRBgwaAkFw%40mail.gmail.com
> 2. https://www.postgresql.org/message-id/CAH2-Wzm2%3Dnf_JhiM3A2yetxRs8Nd2NuN3JqH%3Dfm_YWYd1oYoPg%40mail.gmail.com
> 3. https://www.postgresql.org/message-id/CA%2BTgmoaOiwMXBBTYknczepoZzKTp-Zgk5ss1%2BCuVQE-eFTqBmA%40mail.gmail.com
	
-- 
regards,
Andrei Lepikhov
Postgres Professional