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

From: Alena Rybakina <lena(dot)ribackina(at)yandex(dot)ru>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
Cc: Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>, Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>, teodor(at)sigaev(dot)ru, Peter Geoghegan <pg(at)bowt(dot)ie>
Subject: Re: POC, WIP: OR-clause support for indexes
Date: 2023-07-05 19:39:08
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

Alena Rybakina
Postgres Professional

Attachment Content-Type Size
time_comsuption_with_indexes.png image/png 78.9 KB
time_comsuption_without_indexes.png image/png 78.9 KB
0001-Replace-OR-clause-to-ANY-expressions.patch text/x-patch 10.0 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Jelte Fennema 2023-07-05 19:39:40 Re: Allow specifying a dbname in pg_basebackup connection string
Previous Message Soumyadeep Chakraborty 2023-07-05 18:57:58 Re: brininsert optimization opportunity