ghiureai <isabella.ghiurea@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
