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
Date: Tue, 10 Nov 2015 12:05:30 -0500
Message-ID: <15009.1447175130@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, david@qcode.co.uk
Subject: Re: Slow 3 Table Join with v bad row estimate
In-Reply-To: <15009.1447175130@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox