On Wed, 5 Jun 2019 at 04:55, Rob Northcott <Rob.Northcott@compilator.com> wrote:
> Explain files attached (assuming attachments will get through to the group – otherwise what’s the best way to post
it?)
You're best to post the EXPLAIN ANALYZE output to
https://explain.depesz.com what you attached is pretty horrible to
read and details are only gained by hovering the mouse cursor over the
node. I for one struggle to work out what's the inner and outer sides
of the join with the output you've given and can only work it out by
looking at the nested loop plan to see which side the parameter is on.
It appears that the sub-query plan has changed from a Nested Loop plan
to a Merge Join. The Merge Join is a pretty bad plan since the index
that provides the pre-sorted input must filter out many non-matching
rows. It's not quite clear to me why the planner chooses that index,
mostly because I'm too lazy to learn the output you've shown the plans
in, but if you did have an index on sales_invoicedetails (std_stk_key,
std_unique), then the Merge Join plan would likely produce a better
plan, or at least better than the current Merge Join plan.
You'll likely want to ensure that random_page_cost has not been set to
something insane on the Merge Join instance. I'd also check
seq_page_cost too and also effective_cache_size.
More recommendations might be easier to give if you show the plans in
a better format.
Apart from that, you could consider also instead of performing a
sub-query, LEFT JOIN to a query similar to your subquery but after
removing the std_stk_key = stock.stk_key condition and adding a GROUP
BY std_stk_key. However, that may perform worse if there are many more
std_stk_key groups than there are matching rows in stock.stk_key.
Experimentation might be required there.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
