-----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----- -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
