On Sat, 29 Dec 2018 at 04:32, Justin Pryzby <pryzby@telsasoft.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.