Re: PG 9.5 same SQL 2 different plans

ghiureai <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)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