From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Osborne <david@qcode.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow 3 Table Join with v bad row estimate
Date: Tue, 10 Nov 2015 10:03:29 -0500
Message-ID: <2570.1447167809@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKmpXCciVMDYTtRRF1ADHJU=3MN52QKGk--78W4NnHw3wrzFLQ@mail.gmail.com>
References: <CAKmpXCciVMDYTtRRF1ADHJU=3MN52QKGk--78W4NnHw3wrzFLQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
David Osborne <david@qcode.co.uk> writes:
> We have 3 different ways we have to do the final X join condition (we use 3
> subqueries UNIONed together), but the one causing the issues is:
> (o.branch_code || o.po_number = replace(ss.order_no,' ',''))
> ... So we can see straight away that the outer Nested loop expects 1 row, and
> gets 53595. This isn't going to help the planner pick the most efficient
> plan I suspect.
> I've tried increasing default_statistics_target to the max and re analysing
> all the tables involved but this does not help the estimate.
> I suspect it's due to the join being based on functional result meaning any
> stats are ignored?
Yeah, the planner is not nearly smart enough to draw any useful
conclusions about the selectivity of that clause from standard statistics.
What you might try doing is creating functional indexes on the two
subexpressions:
create index on branch_purchase_order ((branch_code || po_number));
create index on stocksales_ib (replace(order_no,' ',''));
(actually it looks like you've already got the latter one) and then
re-ANALYZING. I'm not necessarily expecting that the planner will
actually choose to use these indexes in its plan; but their existence
will prompt ANALYZE to gather stats about the expression results,
and that should at least let the planner draw more-accurate conclusions
about the selectivity of the equality constraint.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, david@qcode.co.uk
Subject: Re: Slow 3 Table Join with v bad row estimate
In-Reply-To: <2570.1447167809@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox