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 <https://github.com/postgres/postgres/blob/master/src/backend/optimizer/path/indxpath.c#L1878> 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? Even if I am wrong, and the condition absolutely should be bms_overlap, I feel that this restriction is very very hard to discover and perhaps https://www.postgresql.org/docs/current/indexes-bitmap-scans.html should mention that compound indexes that have columns in common will never be combined? Best regards, Dmytro
