Horton

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

Alena Rybakina <a.rybakina@postgrespro.ru>

View thread
From: Alena Rybakina <a.rybakina@postgrespro.ru>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Alexander Korotkov <aekorotkov@gmail.com>, jian he <jian.universality@gmail.com>, pgsql-hackers@postgresql.org, Marcos Pegoraro <marcos@f10.com.br>, teodor@sigaev.ru, Andrei Lepikhov <a.lepikhov@postgrespro.ru>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Peter Eisentraut <peter@eisentraut.org>, Ranier Vilela <ranier.vf@gmail.com>
Date: 2024-06-27T20:06:49Z
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,

Tobe honest,I've alreadystartedwritingcodetodothis,butI'm facedwitha 
misunderstandingof howto correctlycreatea 
conditionfor"OR"expressionsthatare notsubjectto transformation.
>
> For example,the expressions b=1in the query below:
>
> alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = 
> ANY(ARRAY[5,4])) or (b=1); QUERY PLAN 
> ---------------------------------------------------------------------------------- 
> Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR 
> (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows)
>
> I see that two expressions have remained unchanged and it only works 
> for "AND" binary operations.
>
> But I think it might be worth applying this together, where does the 
> optimizer generate indexes (build_paths_for_OR function)?
>

Sorry, it works) I needed to create one more index for b column.

Just in case, I gave an example of a complete case, otherwise it might 
not be entirely clear:

alena@postgres=# create table x (a int, b int);
CREATE TABLE
alena@postgres=# create index a_idx on x(a);
                         insert into x select id,id from 
generate_series(1, 5000) as id;
CREATE INDEX
INSERT 0 5000
alena@postgres=# analyze;
ANALYZE

alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = 
ANY(ARRAY[5,4])) or (b=1); QUERY PLAN 
---------------------------------------------------------------------------------- 
Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR 
(a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows)

alena@postgres=# create index b_idx on x(b);

CREATE INDEX

alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = 
ANY(ARRAY[5,4]))  or (b=1);
                                 QUERY PLAN
--------------------------------------------------------------------------
  Bitmap Heap Scan on x  (cost=12.87..21.68 rows=1 width=8)
    Recheck Cond: ((a = ANY ('{5,4}'::integer[])) OR (b = 1))
    ->  BitmapOr  (cost=12.87..12.87 rows=3 width=0)
          ->  Bitmap Index Scan on a_idx  (cost=0.00..8.58 rows=2 width=0)
                Index Cond: (a = ANY ('{5,4}'::integer[]))
          ->  Bitmap Index Scan on b_idx  (cost=0.00..4.29 rows=1 width=0)
                Index Cond: (b = 1)
(7 rows)

-- 
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company