From: Tom Lane <tgl@sss.pgh.pa.us>
To: ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: PG 9.5 same SQL 2 different plans
Date: Thu, 04 Aug 2016 17:40:45 -0400
Message-ID: <12340.1470346845@sss.pgh.pa.us> (raw)
In-Reply-To: <57A26E16.6010300@nrc-cnrc.gc.ca>
References: <57A26E16.6010300@nrc-cnrc.gc.ca>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
ghiureai <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> we upgrade to PG 9.5.3, in last days we are seeing a strange
> optimization issues with one of the SQL :
> running same SQL every 15-20 times optimizer will choose( wrong
> plan)/ most expensive which generates approx
> 50 GB temp files and runs for aprox 20 min , we can not understand the
> reason ( we run vacuum analyze daily),
It looks like it's flipping between two different plans depending on the
estimate of the number of "planeskeleton" rows matching the particular
"obsid" value you're requesting. The cost estimates for those plans
aren't that far apart (34M units vs 25M), but reality is way different.
> Pg conf values:
> random_page_cost=3.0
> defalult_statistics_taget=100
I think you have two problems here. The big one is that the planner is
way overestimating the actual costs of indexscans, which probably means
your database is entirely held in RAM and you ought to knock
random_page_cost down to 1. (But see the usual caveats that fooling with
cost parameters on the basis of a single example query is dangerous.)
A lesser problem is that the rowcount estimates aren't very close, which
also contributes to overestimating the costs of indexscans. It's possible
that would get better if you increased default_statistics_target, though
it's hard to be sure.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, isabella.ghiurea@nrc-cnrc.gc.ca
Subject: Re: PG 9.5 same SQL 2 different plans
In-Reply-To: <12340.1470346845@sss.pgh.pa.us>
* 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