Re: Query runs forever after upgrading to 9.3

"Andrus" <kobruleht2(at)hot(dot)ee> writes:
>> Just out of curiosity, what "earlier version" was that that was able to
>> run this query quickly?

> It was installed in customer site at May 2012 in Windows 2003 server and
> latest RTM version of Postgres
> x32 in this time was used.

That would probably have been 9.1.something, which makes it even less
likely that there was a significant planner difference affecting this.
I wonder if maybe the 9.1 installation had a higher work_mem, or there
was some other configuration setting you forgot to bring forward.
(A higher work_mem might have allowed it to use hashed rather than
simple subplans, which could possibly explain the speed difference.)

> Will your suggestion run faster ? Is it reasonable to switch to use your
> suggestion ?

If you're happy with performance now, there's probably no reason to
mess with it. Changing the query might allow you to skip building
those indexes though, so if that's a pain point then it might be
worth spending more time on.

regards, tom lane