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

Alena Rybakina <lena.ribackina@yandex.ru>

From: Alena Rybakina <lena.ribackina@yandex.ru>

To: Peter Geoghegan <pg@bowt.ie>

Cc: Marcos Pegoraro <marcos@f10.com.br>, Alena Rybakina <a.rybakina@postgrespro.ru>, Andrey Lepikhov <a.lepikhov@postgrespro.ru>, pgsql-hackers@postgresql.org, teodor@sigaev.ru

Date: 2023-06-27T15:49:20Z

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 →

Make group_similar_or_args() reorder clause list as little as possible
- 775a06d44c04 18.0 landed
Allow usage of match_orclause_to_indexcol() for joins
- 627d63419e22 18.0 landed
Skip not SOAP-supported indexes while transforming an OR clause into SAOP
- 5bba0546eecb 18.0 landed
Remove the wrong assertion from match_orclause_to_indexcol()
- d4d11940df94 18.0 landed
Teach bitmap path generation about transforming OR-clauses to SAOP's
- ae4569161a27 18.0 landed
Transform OR-clauses to SAOP's during index matching
- d4378c0005e6 18.0 landed
Fix the value of or_to_any_transform_limit in postgresql.conf.sample
- 2af75e117478 17.0 landed
Transform OR clauses to ANY expression
- 72bd38cc99a1 17.0 landed
MergeAttributes code deduplication
- 64444ce071f6 17.0 cited
SEARCH and CYCLE clauses
- 3696a600e229 14.0 cited
Improve estimation of OR clauses using extended statistics.
- 25a9e54d2db3 14.0 cited
Teach btree to handle ScalarArrayOpExpr quals natively.
- 9e8da0f75731 9.2.0 cited
Revise collation derivation method and expression-tree representation.
- b310b6e31ce5 9.1.0 cited
Instead of trying to force WHERE clauses into CNF or DNF normal form,
- 9888192fb773 8.0.0 cited

Attachments

graphs.html (text/html)
graphs.png (image/png)

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)
> \\
> -- 
> Regards,
> Alena Rybakina
Sorry, just now I noticed that there were incorrect names in the 
headings of the pictures, I corrected it. I also attach its html copy, 
because it may be more convenient for viewing it.

-- 
Regards,
Alena Rybakina