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

From Tom Lane
Subject Re: Slow 3 Table Join with v bad row estimate
Date
Msg-id 15009.1447175130@sss.pgh.pa.us
Whole thread Raw
In response to Re: Slow 3 Table Join with v bad row estimate  (David Osborne <david@qcode.co.uk>)
Responses Re: Slow 3 Table Join with v bad row estimate  (David Osborne <david@qcode.co.uk>)
List pgsql-performance
David Osborne <david@qcode.co.uk> writes:
> 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.

Meh.  In that case, likely the explanation is that the various conditions
in your query are highly correlated, and the planner is underestimating
the number of rows that will satisfy them because it doesn't know about
the correlation.

But taking a step back, it seems like the core problem in your explain
output is here:

>>    ->  Nested Loop  (cost=1.29..83263.71 rows=1 width=24) (actual time=0.196..23799.930 rows=53595 loops=1)
>>          Join Filter: (o.po_id = p.po_id)
>>          Rows Removed by Join Filter: 23006061
>>          Buffers: shared hit=23217993 dirtied=1

That's an awful lot of rows being formed by the join only to be rejected.
You should try creating an index on
branch_purchase_order_products(po_id, product_code)
so that the po_id condition could be enforced at the inner indexscan
instead of the join.

            regards, tom lane


pgsql-performance by date:

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