Re: Slow 3 Table Join with v bad row estimate

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 <tgl(at)sss(dot)pgh(dot)pa(dot)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
>