pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: tcook@blackducksoftware.com
Subject: BUG #16324: bad cost estimates for generic query plans
Date: Fri, 27 Mar 2020 15:29:29 +0000
Message-ID: <16324-32ad11f2ab803917@postgresql.org> (raw)

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.



view thread (3+ messages)  latest in thread

Message-ID: <16324-32ad11f2ab803917@postgresql.org>
Permalink:  ../16324-32ad11f2ab803917@postgresql.org/
Also on:    postgresql.org/message-id/16324-32ad11f2ab803917@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, tcook@blackducksoftware.com
  Subject: Re: BUG #16324: bad cost estimates for generic query plans
  In-Reply-To: <16324-32ad11f2ab803917@postgresql.org>
  References: <16324-32ad11f2ab803917@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox