From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: udv.mail@gmail.com
Subject: BUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan.
Date: Sat, 20 Nov 2021 16:52:50 +0000
Message-ID: <17295-82a33e30e35c0cd5@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, udv.mail@gmail.com
Subject: Re: BUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan.
In-Reply-To: <17295-82a33e30e35c0cd5@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox