BUG #16324: bad cost estimates for generic query plans - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16324
Logged by:          Todd Cook
Email address:      tcook@blackducksoftware.com
PostgreSQL version: 11.7
Operating system:   CentOS 7.7
Description:

With PG 11.7, we're seeing bad cost estimates for generic query plans where
the cost of
a very expensive InitPlan is not included in the total cost.

    test=# select version() ;
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-39), 64-bit


The initial custom plan is very good:

    test=# prepare s1 as SELECT EXISTS(SELECT 1 FROM audit_event WHERE id > $1
AND event_name IN ($2,$3,$4,$5,$6,$7,$8,$9,$10)) ;
    PREPARE
    test=# explain analyze execute s1(316945699, 'CVA', 'CVCC', 'CVIC',
'CVRDC', 'CVR', 'CVSC', 'CVTC', 'CBE', 'VBCLBC') ;
                                                                  QUERY PLAN
    
           

--------------------------------------------------------------------------------------------------------------------------------------
     Result  (cost=4.60..4.61 rows=1 width=1) (actual time=0.009..0.009 rows=1
loops=1)
       InitPlan 1 (returns $0)
         ->  Index Scan using audit_event_pkey on audit_event  (cost=0.57..4.60
rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)
               Index Cond: (id > '316945699'::bigint)
               Filter: (event_name = ANY
('{CVA,CVCC,CVIC,CVRDC,CVR,CVSC,CVTC,CBE,VBCLBC}'::text[]))
     Planning Time: 0.403 ms
     Execution Time: 0.033 ms
    (7 rows)

The audit_event table has 82 million rows, and the listed event_names
account for about
15 million of them.  However, 316945699 is the maximum id value, so the
existence check
returns false.

Then, after 5 invocations, PG switches to using a cached, generic query plan
that is
very slow:

    test=# explain analyze execute s1(316945699, 'CVA', 'CVCC', 'CVIC',
'CVRDC', 'CVR', 'CVSC', 'CVTC', 'CBE', 'VBCLBC') ;
                                                                QUERY PLAN
    
         

----------------------------------------------------------------------------------------------------------------------------------
     Result  (cost=0.47..0.48 rows=1 width=1) (actual time=28314.960..28314.961
rows=1 loops=1)
       InitPlan 1 (returns $0)
         ->  Seq Scan on audit_event  (cost=0.00..6796877.67 rows=14532272
width=0) (actual time=28314.953..28314.953 rows=0 loops=1)
               Filter: ((id > $1) AND (event_name = ANY (ARRAY[$2, $3, $4, $5, $6,
$7, $8, $9, $10])))
               Rows Removed by Filter: 82349547
     Planning Time: 0.377 ms
     Execution Time: 28315.003 ms
    (7 rows)

It looks like the total cost of the plan is not including the substantial
cost of
the InitPlan.

FWIW, 9.6.17 exhibits the same behavior.