Re: Query Performance Issue

On Sat, 29 Dec 2018 at 04:32, Justin Pryzby <pryzby(at)telsasoft(dot)com> 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