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@xxxxxxxxxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
