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

______________________________________________

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