Re: POC, WIP: OR-clause support for indexes - Mailing list pgsql-hackers

From Alena Rybakina
Subject Re: POC, WIP: OR-clause support for indexes
Date
Msg-id 938d82e1-98df-6553-334c-9db7c4e288ae@yandex.ru
Whole thread Raw
In response to Re: POC, WIP: OR-clause support for indexes  (Alena Rybakina <lena.ribackina@yandex.ru>)
Responses Re: POC, WIP: OR-clause support for indexes
Re: POC, WIP: OR-clause support for indexes
List 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.
>
-- 
Regards,
Alena Rybakina
Postgres Professional

Attachment

pgsql-hackers by date:

Previous
From: Jacob Champion
Date:
Subject: Re: [PATCH] Honor PG_TEST_NOCLEAN for tempdirs
Next
From: Fabrízio de Royes Mello
Date:
Subject: Re: Including a sample Table Access Method with core code