From: Rafael Martinez <r.m.guerrero@usit.uio.no>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: SQL statement over 500% slower with 9.2 compared with 9.1
Date: Wed, 28 Aug 2013 12:15:26 +0200
Message-ID: <521DCDBE.1000901@usit.uio.no> (raw)
In-Reply-To: <CAMkU=1xQTqZvJed7cVzGo6soNv=94BAJo-Crof5NuGqYfABfCA@mail.gmail.com>
References: <521B4B2A.5010800@usit.uio.no>
<CAMkU=1xQTqZvJed7cVzGo6soNv=94BAJo-Crof5NuGqYfABfCA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
-----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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: r.m.guerrero@usit.uio.no, jeff.janes@gmail.com
Subject: Re: SQL statement over 500% slower with 9.2 compared with 9.1
In-Reply-To: <521DCDBE.1000901@usit.uio.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox