Message-ID: <521C6F1F.5070901@usit.uio.no>
Date: Tue, 27 Aug 2013 11:19:27 +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: pgsql-performance@postgresql.org
Subject: Re: SQL statement over 500% slower with 9.2 compared with
 9.1
References: <521B4B2A.5010800@usit.uio.no>
In-Reply-To: <521B4B2A.5010800@usit.uio.no>
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/26/2013 02:33 PM, Rafael Martinez wrote:
[............]
> The SQL statement is:
> 
> SELECT  firstname || ' ' || lastname AS Name FROM    Person R WHERE
> R.gender like 'F' AND  19 < (SELECT COUNT(DISTINCT filmId) FROM
> FilmParticipation F WHERE  F.partType = 'director' AND F.personId =
> R.personId    ) AND NOT EXISTS ( SELECT  * 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 ) ) ;
> 
> 
[.............]
> 
> We can see that the query plan is very different between versions
> and that 9.2 is really wrong with the number of rows involved. Why
> is 9.2 taking so wrong about the number of rows involved in some
> parts of the plan?
> 

Hei

More information:

If we turn off enable_indexscan the runtime gets more similar to the
one we get with 9.1, we are down to 4200ms.

The query plan with this configuration is here:
http://explain.depesz.com/s/jVR

The question remains the same, why is 9.2 using such a different and
bad plan compared to 9.1, when the data and the configuration are the
same?

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)

iEYEARECAAYFAlIcbx8ACgkQBhuKQurGihReJgCcCiEfGQ0rZHcazlN3Ihb2PeCn
jOsAnjkh1M0j4r1DQJ4Xb1djZ+y4mji3
=Td8b
-----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