From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Osborne <david@qcode.co.uk>
cc: pgsql-performance@postgresql.org
Subject: Re: Slow 3 Table Join with v bad row estimate
In-reply-to: 
 <CAKmpXCcjAPTkTExkLCdax__YJ5cdRPjcG7i7VnPn6_U1wzftZg@mail.gmail.com>
References: 
 <CAKmpXCciVMDYTtRRF1ADHJU=3MN52QKGk--78W4NnHw3wrzFLQ@mail.gmail.com>
 <2570.1447167809@sss.pgh.pa.us>
 <CAKmpXCcjAPTkTExkLCdax__YJ5cdRPjcG7i7VnPn6_U1wzftZg@mail.gmail.com>
Comments: In-reply-to David Osborne <david@qcode.co.uk>
 message dated "Tue, 10 Nov 2015 16:38:37 +0000"
Date: Tue, 10 Nov 2015 12:05:30 -0500
Message-ID: <15009.1447175130@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance