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) 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 <t...@sss.pgh.pa.us> 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 >
