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