pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan.
2+ messages / 2 participants
[nested] [flat]
* BUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan.
@ 2021-11-20 16:52 PG Bug reporting form <noreply@postgresql.org>
2021-11-21 23:37 ` Re: BUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan. Tomas Vondra <tomas.vondra@enterprisedb.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2021-11-20 16:52 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: udv.mail@gmail.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan.
2021-11-20 16:52 BUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan. PG Bug reporting form <noreply@postgresql.org>
@ 2021-11-21 23:37 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tomas Vondra @ 2021-11-21 23:37 UTC (permalink / raw)
To: udv.mail@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Hi,
I'm not really sure which part you condides a bug? We simply don't
translate between IN() and OR-ed conditions, and there may be some
costing differences. So we may generate different plans, and the switch
between index scan and bitmap index scan may happen at different points.
On 11/20/21 17:52, PG Bug reporting form wrote:
>
> 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[]))
> |
This is rather strange, though. Why would this have estimate 200000? The
other query has estimate 4, so it can't be because of missing stats etc.
Or why would it fix after several minutes?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-11-21 23:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-20 16:52 BUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan. PG Bug reporting form <noreply@postgresql.org>
2021-11-21 23:37 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
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