Re: POC, WIP: OR-clause support for indexes
| От | Alexander Korotkov |
|---|---|
| Тема | Re: POC, WIP: OR-clause support for indexes |
| Дата | |
| Msg-id | CAPpHfdtSXxhdv3mLOLjEewGeXJ+Ftfhjqodn1WWuq5JLsKx48g@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: POC, WIP: OR-clause support for indexes (Andrei Lepikhov <a.lepikhov@postgrespro.ru>) |
| Ответы |
Re: POC, WIP: OR-clause support for indexes
(Andrei Lepikhov <a.lepikhov@postgrespro.ru>)
Re: POC, WIP: OR-clause support for indexes (Andrei Lepikhov <a.lepikhov@postgrespro.ru>) |
| Список | pgsql-hackers |
On Wed, Mar 13, 2024 at 2:16 PM Andrei Lepikhov <a.lepikhov@postgrespro.ru> wrote:
> On 13/3/2024 18:05, Alexander Korotkov wrote:
> > On Wed, Mar 13, 2024 at 7:52 AM Andrei Lepikhov
> > Given all of the above, I think moving transformation to the
> > canonicalize_qual() would be the right way to go.
> Ok, I will try to move the code.
> I have no idea about the timings so far. I recall the last time I got
> bogged down in tons of duplicated code. I hope with an almost-ready
> sketch, it will be easier.
Thank you! I'll be looking forward to the updated patch.
I also have notes about the bitmap patch.
/*
* Building index paths over SAOP clause differs from the logic of OR clauses.
* Here we iterate across all the array elements and split them to SAOPs,
* corresponding to different indexes. We must match each element to an index.
*/
This covers the case I posted before. But in order to fix all possible cases we probably need to handle the SAOP clause in the same way as OR clauses. Check also this case.
> On 13/3/2024 18:05, Alexander Korotkov wrote:
> > On Wed, Mar 13, 2024 at 7:52 AM Andrei Lepikhov
> > Given all of the above, I think moving transformation to the
> > canonicalize_qual() would be the right way to go.
> Ok, I will try to move the code.
> I have no idea about the timings so far. I recall the last time I got
> bogged down in tons of duplicated code. I hope with an almost-ready
> sketch, it will be easier.
Thank you! I'll be looking forward to the updated patch.
I also have notes about the bitmap patch.
/*
* Building index paths over SAOP clause differs from the logic of OR clauses.
* Here we iterate across all the array elements and split them to SAOPs,
* corresponding to different indexes. We must match each element to an index.
*/
This covers the case I posted before. But in order to fix all possible cases we probably need to handle the SAOP clause in the same way as OR clauses. Check also this case.
Setup
create table t (a int not null, b int not null, c int not null);
insert into t (select 1, 1, i from generate_series(1,10000) i);
insert into t (select i, 2, 2 from generate_series(1,10000) i);
create index t_a_b_idx on t (a, b);
insert into t (select i, 2, 2 from generate_series(1,10000) i);
create index t_a_b_idx on t (a, b);
create statistics t_a_b_stat (mcv) on a, b from t;
create statistics t_b_c_stat (mcv) on b, c from t;
create statistics t_b_c_stat (mcv) on b, c from t;
vacuum analyze t;
Plan with enable_or_transformation = on:
# explain select * from t where a = 1 and (b = 1 or b = 2) and c = 2;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=156.55..440.56 rows=5001 width=12)
Recheck Cond: (a = 1)
Filter: ((b = ANY ('{1,2}'::integer[])) AND (c = 2))
-> Bitmap Index Scan on t_a_b_idx (cost=0.00..155.29 rows=10001 width=0)
Index Cond: (a = 1)
(5 rows)
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=156.55..440.56 rows=5001 width=12)
Recheck Cond: (a = 1)
Filter: ((b = ANY ('{1,2}'::integer[])) AND (c = 2))
-> Bitmap Index Scan on t_a_b_idx (cost=0.00..155.29 rows=10001 width=0)
Index Cond: (a = 1)
(5 rows)
Plan with enable_or_transformation = off:
# explain select * from t where a = 1 and (b = 1 or b = 2) and c = 2;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=11.10..18.32 rows=5001 width=12)
Recheck Cond: (((b = 1) AND (c = 2)) OR ((a = 1) AND (b = 2)))
Filter: ((a = 1) AND (c = 2))
-> BitmapOr (cost=11.10..11.10 rows=2 width=0)
-> Bitmap Index Scan on t_b_c_idx (cost=0.00..4.30 rows=1 width=0)
Index Cond: ((b = 1) AND (c = 2))
-> Bitmap Index Scan on t_a_b_idx (cost=0.00..4.30 rows=1 width=0)
Index Cond: ((a = 1) AND (b = 2))
(8 rows)
As you can see this case is not related to partial indexes. Just no index selective for the whole query. However, splitting scan by the OR qual lets use a combination of two selective indexes.
------
Regards,
Alexander Korotkov
------------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=11.10..18.32 rows=5001 width=12)
Recheck Cond: (((b = 1) AND (c = 2)) OR ((a = 1) AND (b = 2)))
Filter: ((a = 1) AND (c = 2))
-> BitmapOr (cost=11.10..11.10 rows=2 width=0)
-> Bitmap Index Scan on t_b_c_idx (cost=0.00..4.30 rows=1 width=0)
Index Cond: ((b = 1) AND (c = 2))
-> Bitmap Index Scan on t_a_b_idx (cost=0.00..4.30 rows=1 width=0)
Index Cond: ((a = 1) AND (b = 2))
(8 rows)
As you can see this case is not related to partial indexes. Just no index selective for the whole query. However, splitting scan by the OR qual lets use a combination of two selective indexes.
------
Regards,
Alexander Korotkov
В списке pgsql-hackers по дате отправления:
Следующее
От: Jelte Fennema-NioДата:
Сообщение: Re: [EXTERNAL] Re: Add non-blocking version of PQcancel