From: Shaun Thomas <sthomas@optionshouse.com>
To: Jeff Janes <jeff.janes@gmail.com>
To: Aaron Weber <aweber@comcast.net>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: how to improve perf of 131MM row table?
Date: Thu, 26 Jun 2014 15:26:43 -0500
Message-ID: <53AC8203.5030809@optionshouse.com> (raw)
In-Reply-To: <CAMkU=1yxskTJ8ZQQZvBqJ82Jk_R_NoeuCKkYv+8aunT191=Htw@mail.gmail.com>
References: <53AB2CB1.8080201@comcast.net>
<53AB35CC.10202@optionshouse.com>
<1801a5d4-c3f1-4c0c-9c2c-ffb9d7f723a3@email.android.com>
<CAMkU=1yxskTJ8ZQQZvBqJ82Jk_R_NoeuCKkYv+8aunT191=Htw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 06/26/2014 03:14 PM, Jeff Janes wrote:
> If that is 50 PKs from the master table, it would be about 1000 on the
> detail table.
You're right. But here's the funny part: we solved this after we noticed
his where clause was directed at the *detail* table instead of the
master table. This was compounded by the fact the planner incorrectly
estimated the row match count on the detail table due to the well-known
correlation deficiencies especially present in older versions. The row
count went from 1000 to 50,000.
Then it joined against the master table. Since 50,000 index page fetches
followed by 50,000 data page fetches would be pretty damn slow, the
planner went for a sequence scan on the master table instead. Clearly
the old 9.0 planner does not consider transitive IN equality.
I'm curious to see if Aaron can test his structure on 9.3 with the
original data and WHERE clause and see if the planner still goes for the
terrible plan. If it does, that would seem like an obvious planner tweak
to me.
--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
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: sthomas@optionshouse.com, jeff.janes@gmail.com, aweber@comcast.net
Subject: Re: how to improve perf of 131MM row table?
In-Reply-To: <53AC8203.5030809@optionshouse.com>
* 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