Обсуждение: BUG #17295: Different query plan with Index Only Scan and Bitmap Index Scan.

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.

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