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.