Thanks very much Tom.
Doesn't seem to quite do the trick. I created both those indexes (or the missing one at least)
Then I ran analyse on stocksales_ib and branch_purchase_order.
I checked there were stats held in pg_stats for both indexes, which there were.
But the query plan still predicts 1 row and comes up with the same plan.
I also tried setting default_statistics_target to 10000 and reran analyse on both tables with the same results.
In addition, also no change if I change the query to have the join ss.order_no=o.branch_code || ' ' || o.po_number and create an index on (branch_code || ' ' || o.po_number)
In addition, also no change if I change the query to have the join ss.order_no=o.branch_code || ' ' || o.po_number and create an index on (branch_code || ' ' || o.po_number)
Am I right in thinking my workaround with the WITH clause is in no way guaranteed to continue to perform better than the current query if I rolled that out?
On 10 November 2015 at 15:03, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
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
