PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15984
PG Version11.5
OSDebian Sid
Opened2019-08-29 15:48:46+00
Reported byRichard
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15984
Logged by:          Richard
Email address:      (redacted)
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                                                
                                                              │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Messages

DateAuthorSubject
2019-08-29 15:48:46+00PG Bug reporting formBUG #15984: order of where in() query affects query planer
2019-08-29 22:16:45+00Tom LaneRe: BUG #15984: order of where in() query affects query planer
2019-08-30 06:54:03+00easteregg(at)verfriemelt(dot)orgRe: BUG #15984: order of where in() query affects query planer
2019-09-02 09:28:07+00easteregg(at)verfriemelt(dot)orgRe: BUG #15984: order of where in() query affects query planer