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

On 06/26/2014 11:19 AM, Claudio Freire wrote:

> Try changing node_id in (...) into node.id in (...)

Wow. How did we not see that earlier? That's probably the issue. If you
look at the estimates of his query:

Bitmap Heap Scan on alf_node_properties prop (cost=1253.19..189491.87
rows=52790 width=179) (actual time=0.571..1.349 rows=1071 loops=1)

The planner is off by an order of magnitude, and since the matches are
against node_id instead of node.id, it thinks it would have to index
seek on the alf_node table for over 50k rows. I could easily see it
opting for a sequence scan in that case, depending on how high
random_page_cost is.

--
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