Re: Query plan good in 8.4, bad in 9.2 and better in 9.3

On Thu, May 15, 2014 at 9:35 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> OK so we have a query that does OK in 8.4, goes to absolute crap in
> 9.2 and then works great in 9.3. Thing is we've spent several months
> regression testing 9.2 and no time testing 9.3, so we can't just "go
> to 9.3" in an afternoon. But we might have to. 9.2 seems hopelessly
> broken here.
>
> The query looks something like this:
>
> SELECT COUNT(*) FROM u, ug
> WHERE u.ugid = ug.id
> AND NOT u.d
> AND ug.somefield IN (SELECT somefunction(12345));
>
> In 8.4 we get this plan http://explain.depesz.com/s/r3hF which takes ~5ms
> In 9.2 we get this plan http://explain.depesz.com/s/vM7 which takes ~10s
> In 9.3 we get this plan http://explain.depesz.com/s/Wub which takes
> ~0.35ms
>

Based on the actual row counts given in the seq scan on u, , in 9.2, u
contains millions of rows. In 9.3, it contains zero rows.

>
> The data sets are identical, the schemas are identical.

Please double check that.

Cheers,

Jeff