Horton

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

Alexander Korotkov <aekorotkov@gmail.com>

View thread
From: Alexander Korotkov <aekorotkov@gmail.com>
To: Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: Nikolay Shaplov <dhyan@nataraj.su>, pgsql-hackers@lists.postgresql.org, Andrei Lepikhov <a.lepikhov@postgrespro.ru>, jian he <jian.universality@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-07-17T00:03:03Z
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

Hi, Alena!

On Thu, Jul 11, 2024 at 7:17 PM Alena Rybakina
<a.rybakina@postgrespro.ru> wrote:
> I have finished patch and processed almost your suggestions (from [0], [1], [2]). It remains only to add tests where the conversion should work, but I will add this in the next version.
>
> [0] https://www.postgresql.org/message-id/3381819.e9J7NaK4W3%40thinkpad-pgpro
>
> [1] https://www.postgresql.org/message-id/9736220.CDJkKcVGEf%40thinkpad-pgpro
>
> [2] https://www.postgresql.org/message-id/2193851.QkHrqEjB74%40thinkpad-pgpro

I dare making another revision of this patch.  In this version I moved
the transformation to match_clause_to_indexcol().  Therefore, this
allows to successfully construct index scans with SAOP, but has no
degradation in generation of bitmap scans which I observed in [1] and
[2].  BTW, I found that my description in [2] lacks of t_b_c_idx index
definition. Sorry for that.

Given that now we're doing OR-to-ANY transformation solely to match an
index we don't need complex analysis of OR-list, which potentially
could take quadratic time.  Instead, we're trying to match every OR
element to an index and quit immediately on failure.

I'd like to head a feedback on the new place to apply the
transformation.  It looks like significant simplification for me and
the way to go.

Also, I have addressed some of notes by Robert Haas [3].  In v27 we
don't use expression evaluation, but directly construct an array
constant when possible.  Also we don't transform operator id to string
and back, but directly construct SAOP instead.

Links.
1. https://www.postgresql.org/message-id/CAPpHfduJtO0s9E%3DSHUTzrCD88BH0eik0UNog1_q3XBF2wLmH6g%40mail.gmail.com
2. https://www.postgresql.org/message-id/CAPpHfdtSXxhdv3mLOLjEewGeXJ%2BFtfhjqodn1WWuq5JLsKx48g%40mail.gmail.com
3. https://www.postgresql.org/message-id/CA%2BTgmobu0DUFCTF28DuAi975mEc4xYqX3xyt8RA0WbnyrYg%2BFw%40mail.gmail.com

------
Regards,
Alexander Korotkov
Supabase