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

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(at)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(at)postgres=# create table x (a int, b int);
CREATE TABLE
alena(at)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(at)postgres=# analyze;
ANALYZE

alena(at)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(at)postgres=# create index b_idx on x(b);

CREATE INDEX

alena(at)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