Horton

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

Alena Rybakina <lena.ribackina@yandex.ru>

View thread
From: Alena Rybakina <lena.ribackina@yandex.ru>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Ranier Vilela <ranier.vf@gmail.com>
Cc: Marcos Pegoraro <marcos@f10.com.br>, Andrey Lepikhov <a.lepikhov@postgrespro.ru>, teodor@sigaev.ru, Peter Geoghegan <pg@bowt.ie>
Date: 2023-07-05T19:39:08Z
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, all!

> On 27.06.2023 16:19, Alena Rybakina wrote:
>> Thank you for your feedback, your work is also very interesting and 
>> important, and I will be happy to review it. I learned something new 
>> from your letter, thank you very much for that!
>>
>> I analyzed the buffer consumption when I ran control regression tests 
>> using my patch. diff shows me that there is no difference between the 
>> number of buffer block scans without and using my patch, as far as I 
>> have seen. (regression.diffs)
>>
>>
>> In addition, I analyzed the scheduling and duration of the execution 
>> time of the source code and with my applied patch. I generated 20 
>> billion data from pgbench and plotted the scheduling and execution 
>> time depending on the number of "or" expressions.
>> By runtime, I noticed a clear acceleration for queries when using the 
>> index, but I can't say the same when the index is disabled.
>> At first I turned it off in this way:
>> 1)enable_seqscan='off'
>> 2)enable_indexonlyscan='off'
>> enable_indexscan='off'
>>
>> Unfortunately, it is not yet clear which constant needs to be set 
>> when the transformation needs to be done, I will still study in 
>> detail. (the graph for all this is presented in graph1.svg

I finished comparing the performance of queries with converted or 
expressions and the original ones and found that about 500 "OR" 
expressions have significantly noticeable degradation of execution time, 
both using the index and without it (you can look at 
time_comsuption_with_indexes.png and time_comsuption_without_indexes.html )

The test was performed on the same benchmark database generated by 2 
billion values.

I corrected this constant in the patch.

-- 
Regards,
Alena Rybakina
Postgres Professional