On Sat, 29 Dec 2018 at 04:32, Justin Pryzby <pryzby@xxxxxxxxxxxxx> wrote: > I think the solution is to upgrade (at least) to PG10 and CREATE STATISTICS > (dependencies). Unfortunately, I don't think that'll help this situation. Extended statistics are currently only handled for base quals, not join quals. See dependency_is_compatible_clause(). It would be interesting to see how far out the estimate is without the version = 1 clause. If just the recommended_content_id clause is underestimated enough it could be enough to have the planner choose the nested loop. Perhaps upping the stats on that column may help, but it may only help so far as to reduce the chances of a nested loop. If the number of distinct recommended_content_id values is higher than the statistic targets and is skewed enough then there still may be some magic values in there that end up causing a bad plan. It would also be good to know what random_page_cost is set to, and also if effective_cache_size isn't set too high. Increasing random_page_cost would help reduce the chances of this nested loop plan, but it's a pretty global change and could also have a negative effect on other queries. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
