BUG #15984: order of where in() query affects query planer - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15984
Logged by:          Richard
Email address:      easteregg@verfriemelt.org
PostgreSQL version: 11.5
Operating system:   Debian Sid
Description:

i have a partial index like in the following example and when reorder the
elements of the in() statement,
i get sometimes a bitmap indexscan instead of the expected index only scan.
if i remove an element, i still get the index only,
but with the wrong order, i get a bitmap heap scan. is this expected?



drop table temp;
create table temp ( i int );
insert into temp
select (random()*20)::int from generate_series(1,1000000,1);
create index "full" on temp( i );
create index "partial" on temp( i ) where i in ( 1,2,3,4,5,6,7,8,9 );
vacuum full temp;  

explain analyse select count(*) from temp where i in ( 1,2,3,4,5,6,7,8,9);


┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                              QUERY PLAN
                                                           │

╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Aggregate  (cost=5336.92..5336.93 rows=1 width=8) (actual
time=164.105..164.105 rows=1 loops=1)
│
│   ->  Index Only Scan using partial on temp  (cost=0.42..5224.42
rows=45000 width=0) (actual time=0.035..138.494 rows=450415 loops=1) │
│         Heap Fetches: 450415
                                                           │
│ Planning Time: 0.953 ms
                                                           │
│ Execution Time: 164.121 ms
                                                           │

└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘


explain analyse select count(*) from temp where i in ( 1,2,3,4,5,6,7,8);


┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                              QUERY PLAN
                                                           │

╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Aggregate  (cost=5336.38..5336.39 rows=1 width=8) (actual
time=170.707..170.707 rows=1 loops=1)
│
│   ->  Index Only Scan using partial on temp  (cost=0.42..5236.38
rows=40000 width=0) (actual time=0.017..144.923 rows=400509 loops=1) │
│         Index Cond: (i = ANY ('{1,2,3,4,5,6,7,8}'::integer[]))
                                                           │
│         Heap Fetches: 400509
                                                           │
│ Planning Time: 0.153 ms
                                                           │
│ Execution Time: 170.722 ms
                                                           │

└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘


explain analyse select count(*) from temp where i in ( 1,2,3,4,5,6,7,9,8);


┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                QUERY PLAN
                                                              │

╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Aggregate  (cost=2641.03..2641.04 rows=1 width=8) (actual
time=313.834..313.835 rows=1 loops=1)
  │
│   ->  Bitmap Heap Scan on temp  (cost=837.50..2528.53 rows=45000 width=0)
(actual time=150.929..262.355 rows=450415 loops=1)             │
│         Recheck Cond: ((i = ANY ('{1,2,3,4,5,6,7,8,9}'::integer[])) AND (i
= ANY ('{1,2,3,4,5,6,7,9,8}'::integer[])))                    │
│         Heap Blocks: exact=4425
                                                              │
│         ->  BitmapAnd  (cost=837.50..837.50 rows=2025 width=0) (actual
time=150.465..150.465 rows=0 loops=1)                             │
│               ->  Bitmap Index Scan on partial  (cost=0.00..349.42
rows=45000 width=0) (actual time=46.848..46.848 rows=450415 loops=1)  │
│               ->  Bitmap Index Scan on "full"  (cost=0.00..465.32
rows=45000 width=0) (actual time=103.481..103.482 rows=450415 loops=1) │
│                     Index Cond: (i = ANY
('{1,2,3,4,5,6,7,9,8}'::integer[]))
                   │
│ Planning Time: 0.121 ms
                                                              │
│ Execution Time: 313.859 ms
                                                              │

└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘