Hi!
I've been investigating why postgres does not do BitmapAnd of two well-suited indexes, and reading indxpath.c
In my case, there is a table (d date, col1 int, col2 int) -- types not really important -- and there are two indices on (d,col1) and (d, col2).
For queries that do WHERE d>=X AND col1=Y AND col2=Z postgres will never BitmapAnd those two indices because both indexes include (d) and we have a condition on (d). Here is a full example, which could also be seen here:
https://www.db-fiddle.com/f/uPLx5bRtDEoZw3Dx4kkwKh/0:
begin;
CREATE TABLE test_table (
d date,
col1 int,
col2 int
);
INSERT INTO test_table (d, col1, col2)
SELECT
d.date,
c1.val as col1,
c2.val as col2
FROM
generate_series(
'2023-01-01'::date,
'2023-12-31'::date,
'1 day'::interval
) as d(date),
generate_series(1, 1000) as c1(val),
generate_series(1, 1000) as c2(val)
WHERE
random() < 0.001;
create index on test_table(col1,d);
create index on test_table(col2,d);
-- This uses BitmapAnd
explain select * from test_table where col1=123 and col2=321;
-- This does not use BitmapAnd, even though it could!
explain select * from test_table where col1=123 and col2=321 and d >= '2023-05-05';
I checked that BitmapAnd is rejected by
this line in choose_bitmap_and:
if (bms_overlap(pathinfo->clauseids, clauseidsofar))
continue; /* consider it redundant */
There is a comment on choose_bitmap_and that explains the rationale of this check, but reading it I can't help but feel that what the comment describes is this condition:
if (bms_is_subset(pathinfo->clauseids, clauseidsofar))
continue; /* consider it redundant */
And indeed, in my (admittedly not super-extensive) testing changing bms_overlap to bms_is_subset leads to better faster execution plans.
Is it possible that this condition could thus be relaxed?
Best regards, Dmytro
