Postgres Pro
Downloads
Home   >   mailing lists

Re: Slow 3 Table Join with v bad row estimate - Mailing list pgsql-performance

From David Osborne
Subject Re: Slow 3 Table Join with v bad row estimate
Date
Msg-id CAKmpXCcjAPTkTExkLCdax__YJ5cdRPjcG7i7VnPn6_U1wzftZg@mail.gmail.com
Whole thread Raw
In response to Re: Slow 3 Table Join with v bad row estimate  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Slow 3 Table Join with v bad row estimate
List pgsql-performance
Tree view
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@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


pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Slow 3 Table Join with v bad row estimate
Next
From: Tom Lane
Date:
Subject: Re: Slow 3 Table Join with v bad row estimate