Message-ID: <53AC8203.5030809@optionshouse.com>
Date: Thu, 26 Jun 2014 15:26:43 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: Jeff Janes <jeff.janes@gmail.com>, Aaron Weber <aweber@comcast.net>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: how to improve perf of 131MM row table?
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>
In-Reply-To: 
 <CAMkU=1yxskTJ8ZQQZvBqJ82Jk_R_NoeuCKkYv+8aunT191=Htw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"; format=flowed
content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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