Re: how to improve perf of 131MM row table?

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(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email