Message-ID: <521DCDBE.1000901@usit.uio.no>
Date: Wed, 28 Aug 2013 12:15:26 +0200
From: Rafael Martinez <r.m.guerrero@usit.uio.no>
Organization: University of Oslo
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130806 Thunderbird/17.0.8
MIME-Version: 1.0
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
References: <521B4B2A.5010800@usit.uio.no>
 <CAMkU=1xQTqZvJed7cVzGo6soNv=94BAJo-Crof5NuGqYfABfCA@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1xQTqZvJed7cVzGo6soNv=94BAJo-Crof5NuGqYfABfCA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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