The following bug has been logged on the website:
Bug reference: 17295
Logged by: Dmitry
Email address: udv.mail@gmail.com
PostgreSQL version: 13.2
Operating system: Debian 10 buster, 4.19.0-14-amd64
Description:
Hello.
As described in documentation "11.5. Combining Multiple Indexes": "To
combine multiple indexes, the system scans each needed index and prepares a
bitmap in memory giving the locations of table rows that are reported as
matching that index's conditions. The bitmaps are then ANDed and ORed
together as needed by the query.".
May be interesting thing, that with using IN instead of ORed conditions,
Postgres generates different plans for queries.
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6)
8.3.0, 64-bit|
Debian 10 buster, 4.19.0-14-amd64 Debian 8.3.0-6
CREATE TABLE t(a INTEGER, b INTEGER);
INSERT INTO t(a,b)
SELECT (100*random()*s.id)::INT, (100*random()*s.id)::INT
FROM generate_series( 1, 1000000 ) AS s( id );
CREATE INDEX t_i ON t(a,b);
EXPLAIN SELECT * FROM t WHERE a=142 OR a=147 OR a=153 OR a=199;
-- Bitmap Heap Scan on t (cost=17.73..33.45 rows=4 width=8) |
-- Recheck Cond: ((a = 142) OR (a = 147) OR (a = 153) OR (a = 199)) |
-- -> BitmapOr (cost=17.73..17.73 rows=4 width=0) |
-- -> Bitmap Index Scan on t_i (cost=0.00..4.43 rows=1 width=0)|
-- Index Cond: (a = 142) |
-- -> Bitmap Index Scan on t_i (cost=0.00..4.43 rows=1 width=0)|
-- Index Cond: (a = 147) |
-- -> Bitmap Index Scan on t_i (cost=0.00..4.43 rows=1 width=0)|
-- Index Cond: (a = 153) |
-- -> Bitmap Index Scan on t_i (cost=0.00..4.43 rows=1 width=0)|
-- Index Cond: (a = 199) |
EXPLAIN SELECT * FROM t WHERE a IN (142,147,153,199);
-- Index Only Scan using t_i on t (cost=0.42..17.77 rows=4 width=8)|
-- Index Cond: (a = ANY ('{142,147,153,199}'::integer[])) |
If we enlarge table with generate_series( 1, 10000000 ), the result
changes:
EXPLAIN SELECT * FROM t WHERE a IN (142,147,153,199);
-- Bitmap Heap Scan on t (cost=3739.74..50987.74 rows=200000 width=8)
|
-- Recheck Cond: (a = ANY ('{142,147,153,199}'::integer[]))
|
-- -> Bitmap Index Scan on t_i (cost=0.00..3689.74 rows=200000
width=0)|
-- Index Cond: (a = ANY ('{142,147,153,199}'::integer[]))
|
Several minutes later:
EXPLAIN SELECT * FROM t WHERE a IN (142,147,153,199);
-- Index Only Scan using t_i on t (cost=0.43..17.81 rows=4 width=8)|
-- Index Cond: (a = ANY ('{142,147,153,199}'::integer[])) |
ORed conditions did not changed, still several Bitmaps.
Best regards, Dmitry.
