Re: strange plan with bitmap heap scan and multiple partial indexes

On 2015-07-11 14:31:25 +0200, Tomas Vondra wrote:
> While working on the "IOS with partial indexes" patch, I've noticed a bit
> strange plan. It's unrelated to that particular patch (reproducible on
> master), so I'm starting a new thread for it.
>
> To reproduce it, all you have to do is this (on a new cluster, all settings
> on default):
>
>   CREATE TABLE t AS SELECT i AS a, i AS b
>                       FROM generate_series(1,10000000) s(i);
>
>   CREATE INDEX idx001 ON t (a) where b < 100;
>   CREATE INDEX idx002 ON t (a) where b < 200;
>   CREATE INDEX idx003 ON t (a) where b < 300;
>
>   ANALYZE t;
>
>   EXPLAIN SELECT a FROM t WHERE b < 100;

>                         QUERY PLAN

It's indeed interesting. Running
ANALYZE t;EXPLAIN SELECT a FROM t WHERE b < 100;
repeatedly switches back and forth between the plans.

Note that
Bitmap Heap Scan on t  (cost=9.01..13.02 rows=1000 width=4)
is actually costed significantly cheaper than
Index Scan using idx001 on t  (cost=0.15..30.32 rows=1000 width=4)
which means yet another wierd thing is that it's not consistently
choosing that plan.


When the index scan plan is chosen you interestingly get the bitmapscan
plan, but at a slightly higher cost:
postgres[32046][1]=# EXPLAIN SELECT a FROM t WHERE b < 100;
┌────────────────────────────────────────────────────────────────────┐
│                             QUERY PLAN                             │
├────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx001 on t  (cost=0.15..30.32 rows=1000 width=4) │
└────────────────────────────────────────────────────────────────────┘
(1 row)

Time: 0.460 ms
postgres[32046][1]=# SET enable_indexscan = false;
SET
Time: 0.119 ms
postgres[32046][1]=# EXPLAIN SELECT a FROM t WHERE b < 100;
┌──────────────────────────────────────────────────────────────────────────────
│                                  QUERY PLAN
│
├──────────────────────────────────────────────────────────────────────────────
│ Bitmap Heap Scan on t  (cost=27.05..31.06 rows=1000 width=4)
│   Recheck Cond: ((b < 300) AND (b < 200))
│   Filter: (b < 100)
│   ->  BitmapAnd  (cost=27.05..27.05 rows=1 width=0)
│         ->  Bitmap Index Scan on idx003  (cost=0.00..13.15 rows=1000 width=0)
│         ->  Bitmap Index Scan on idx002  (cost=0.00..13.15 rows=1000 width=0)
└──────────────────────────────────────────────────────────────────────────────


Looking at the stats is interesting:

postgres[32046][1]=# ANALYZE t;SELECT relpages, reltuples, relallvisible FROM pg_class WHERE relname IN ('t', 'idx003',
'idx002','idx001');EXPLAIN SELECT a FROM t WHERE b < 100;
 
ANALYZE
Time: 123.469 ms
┌──────────┬───────────┬───────────────┐
│ relpages │ reltuples │ relallvisible │
├──────────┼───────────┼───────────────┤
│    44248 │     1e+07 │         44248 │
│        2 │         0 │             0 │
│        2 │       667 │             0 │
│        2 │       667 │             0 │
└──────────┴───────────┴───────────────┘
(4 rows)

Time: 0.405 ms
┌────────────────────────────────────────────────────────────────────┐
│                             QUERY PLAN                             │
├────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx001 on t  (cost=0.12..24.63 rows=1000 width=4) │
└────────────────────────────────────────────────────────────────────┘
(1 row)

Time: 0.269 ms
postgres[32046][1]=# ANALYZE t;SELECT relpages, reltuples, relallvisible FROM pg_class WHERE relname IN ('t', 'idx003',
'idx002','idx001');EXPLAIN SELECT a FROM t WHERE b < 100;
 
ANALYZE
Time: 124.430 ms
┌──────────┬───────────┬───────────────┐
│ relpages │ reltuples │ relallvisible │
├──────────┼───────────┼───────────────┤
│    44248 │     1e+07 │         44248 │
│        2 │         0 │             0 │
│        2 │         0 │             0 │
│        2 │         0 │             0 │
└──────────┴───────────┴───────────────┘
(4 rows)

Time: 0.272 ms
┌──────────────────────────────────────────────────────────────────────────────┐
│                                  QUERY PLAN                                  │
├──────────────────────────────────────────────────────────────────────────────┤
│ Bitmap Heap Scan on t  (cost=9.01..13.02 rows=1000 width=4)                  │
│   Recheck Cond: ((b < 300) AND (b < 200))                                    │
│   Filter: (b < 100)                                                          │
│   ->  BitmapAnd  (cost=9.01..9.01 rows=1 width=0)                            │
│         ->  Bitmap Index Scan on idx003  (cost=0.00..4.13 rows=1000 width=0) │
│         ->  Bitmap Index Scan on idx002  (cost=0.00..4.13 rows=1000 width=0) │
└──────────────────────────────────────────────────────────────────────────────┘
(6 rows)

Time: 0.275 ms
postgres[32046][1]=# ANALYZE t;SELECT relpages, reltuples, relallvisible FROM pg_class WHERE relname IN ('t', 'idx003',
'idx002','idx001');EXPLAIN SELECT a FROM t WHERE b < 100;
 
ANALYZE
Time: 112.592 ms
┌──────────┬─────────────┬───────────────┐
│ relpages │  reltuples  │ relallvisible │
├──────────┼─────────────┼───────────────┤
│    44248 │ 9.99998e+06 │         44248 │
│        2 │           0 │             0 │
│        2 │         334 │             0 │
│        2 │         334 │             0 │
└──────────┴─────────────┴───────────────┘
(4 rows)

Time: 0.330 ms
┌────────────────────────────────────────────────────────────────────┐
│                             QUERY PLAN                             │
├────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx001 on t  (cost=0.12..24.63 rows=1000 width=4) │
└────────────────────────────────────────────────────────────────────┘
(1 row)

Time: 0.320 ms

So this seems to be stats related.