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 20:06:26
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Sorry, I threw off the wrong charts, I'm sending the right ones.

On 05.07.2023 22:39, Alena Rybakina wrote:
> 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 75.6 KB
time_comsuption_without_indexes.png image/png 78.9 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Fabrízio de Royes Mello 2023-07-05 20:22:32 Re: Including a sample Table Access Method with core code
Previous Message Jacob Champion 2023-07-05 19:55:16 Re: [PATCH] Honor PG_TEST_NOCLEAN for tempdirs