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: Marcos Pegoraro <marcos@f10.com.br>, Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: Andrey Lepikhov <a.lepikhov@postgrespro.ru>, pgsql-hackers@postgresql.org, teodor@sigaev.ru
Date: 2023-06-26T02:38:14Z
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

Sorry,  I wrote the last sentence in a confusing way, I meant that I 
formed transformations for any number of "or" expressions 
(const_transform_or_limit=1). in regression tests, I noticed only diff 
changes of transformations of "or" expressions to "any". I attach a file 
with diff.

On 26.06.2023 04:47, Alena Rybakina wrote:
>
> Hi, all! Sorry I haven't written for a long time.
>
> I finished writing the code patch for transformation "Or" expressions 
> to "Any" expressions. I didn't see any problems in regression tests, 
> even when I changed the constant at which the minimum or expression is 
> replaced by any at 0. I ran my patch on sqlancer and so far the code 
> has never fallen.
>
> On 14.01.2023 18:45, Marcos Pegoraro wrote:
>>
>>     I agree with your idea and try to implement it and will soon
>>     attach a patch with a solution.
>>
>> Additionally, if those OR constants repeat you'll see ...
>> If all constants are the same value, fine
>> explain select * from x where ((ID = 1) OR (ID = 1) OR (ID = 1));
>> Index Only Scan using x_id on x  (cost=0.42..4.44 rows=1 width=4)
>>   Index Cond: (id = 1)
>>
>> if all values are almost the same, ops
>> explain select * from x where ((ID = 1) OR (ID = 1) OR (ID = 1) OR 
>> (ID = 2));
>> Bitmap Heap Scan on x  (cost=17.73..33.45 rows=4 width=4)
>>   Recheck Cond: ((id = 1) OR (id = 1) OR (id = 1) OR (id = 2))
>>   ->  BitmapOr  (cost=17.73..17.73 rows=4 width=0)
>>         ->  Bitmap Index Scan on x_id  (cost=0.00..4.43 rows=1 width=0)
>>               Index Cond: (id = 1)
>>         ->  Bitmap Index Scan on x_id  (cost=0.00..4.43 rows=1 width=0)
>>               Index Cond: (id = 1)
>>         ->  Bitmap Index Scan on x_id  (cost=0.00..4.43 rows=1 width=0)
>>               Index Cond: (id = 1)
>>         ->  Bitmap Index Scan on x_id  (cost=0.00..4.43 rows=1 width=0)
>>               Index Cond: (id = 2)
>>
>> thanks
>> Marcos
>>
> --
>
> Regards,
>
> Alena Rybakina
>