Re: Query Performance Issue

On Sat, Dec 29, 2018 at 1:58 AM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> 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().
>
>
But "recommended_content_id" and "version" are both in the same table,
doesn't that make them base quals?

The most obvious thing to me would be to vacuum
product_content_recommendation_main2 to get rid of the massive number of
heap fetches. And to analyze everything to make sure the estimation errors
are not simply due to out-of-date stats. And to increase work_mem.

It isn't clear we want to get rid of the nested loop, from the info we have
to go on the hash join might be even slower yet. Seeing the plan with
enable_nestloop=off could help there.

Cheers,

Jeff