Re: understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr

On 08/05/2014 02:16 PM, john gale wrote:

Your EXPLAIN output basically answered this for you. Your fast query has 
this:

>   Nested Loop  (cost=0.85..2696.12 rows=88 width=1466)

While your slow one has this:

>   Hash Join  (cost=292249.24..348608.93 rows=28273 width=1466)

If this data is at all accurate (run an EXPLAIN ANALYZE to get the real 
numbers), the second query will return about 300x more rows than the 
first one. If your first query takes 5s, 20 minutes for the second isn't 
beyond the realm of possibility. The planner changed approaches because 
an efficient technique to join 88 rows is much different than what's 
needed for 300 times as many.

Given that, five seconds for 88 rows is astonishingly slow, even for 
hstore. I'd be willing to bet something else is going on here.

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