Re: SQL statement over 500% slower with 9.2 compared with 9.1

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/28/2013 06:10 AM, Jeff Janes wrote:
> On Monday, August 26, 2013, Rafael Martinez wrote:

Hei

>
> Could you do explain (analyze, buffers) of these?
>

With 9.1:
http://explain.depesz.com/s/FMe

with 9.2:
http://explain.depesz.com/s/Z1j

>
> What happens if you excise the "19 < (select ...)" clause? That
> would greatly simplify the analysis, assuming the problem remains.
>

With 9.1:
http://explain.depesz.com/s/DhuV

With 9.2:
I do not get a result in a reasonable time, after several minuttes I
cancel the query.

> How many distinct filmId are there?
>

count
- --------
934752

>
> Most directors are not also actors, so there is a strong negative
> correlation that PostgreSQL is not aware of. However, I think if
> you could get 9.1 to report the same path, it would be just as
> wrong on that estimate. But since it doesn't report the same
> path, you don't see how wrong it is.
>
> Try running:
>
> explain (analyze, buffers) SELECT D.personId FROM
> FilmParticipation D WHERE D.partType = 'director' --AND
> D.personId = R.personId AND NOT EXISTS ( SELECT * FROM
> FilmParticipation C WHERE C.partType = 'cast' AND C.filmId =
> D.filmId AND C.personId = D.personId );
>
> On both 9.1 and 9.2.
>

Same result with both:

with 9.1:
http://explain.depesz.com/s/fdO

With 9.2
http://explain.depesz.com/s/gHz

regards,
- --
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAlIdzb4ACgkQBhuKQurGihSGEgCeP6frW7l65IphXFUjw80VMZun
qO0An1++ZB7IGQ0MwR4wphWmlcYGXFDD
=9fg4
-----END PGP SIGNATURE-----