PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16324
PG Version11.7
OSCentOS 7.7
Opened2020-03-27 15:29:29+00
Reported byTodd Cook
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16324
Logged by:          Todd Cook
Email address:      (redacted)
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.

Messages

DateAuthorSubject
2020-03-27 15:29:29+00PG Bug reporting formBUG #16324: bad cost estimates for generic query plans
2020-03-27 16:35:48+00Tom LaneRe: BUG #16324: bad cost estimates for generic query plans
2020-03-27 17:16:35+00"Todd A(dot) Cook"Re: BUG #16324: bad cost estimates for generic query plans