SQL statement over 500% slower with 9.2 compared with 9.1 - Mailing list pgsql-performance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello

We have a SQL statement that with 9.1 takes ca 4000ms to finnish and
with 9.2 over 22000ms.

The explain analyze information is here:

With 9.1.:
http://explain.depesz.com/s/5ou

With 9.2
http://explain.depesz.com/s/d4vU

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


The tables information:

# SELECT count(*) from filmparticipation;
  count
- ----------
 10835351
(1 row)

# SELECT pg_size_pretty(pg_table_size('filmparticipation'));
 pg_size_pretty
- ----------------
 540 MB
(1 row)

# SELECT count(*) from person;
  count
- ---------
 1709384
(1 row)

# SELECT pg_size_pretty(pg_table_size('person'));
 pg_size_pretty
- ----------------
 85 MB
(1 row)


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?

Some additional information:

* VACUUM ANALYZE has been run in both databases.
* Both databases are running on servers running RHEL6.3.
* The relevant parameters changed from the default configuration are:

9.1:
- ----

 checkpoint_segments         | 128
 client_encoding             | UTF8
 effective_cache_size        | 28892MB
 maintenance_work_mem        | 256MB
 max_connections             | 400
 max_stack_depth             | 4MB
 random_page_cost            | 2
 server_encoding             | UTF8
 shared_buffers              | 8026MB
 ssl                         | on
 ssl_renegotiation_limit     | 0
 wal_buffers                 | 16MB
 wal_level                   | archive
 wal_sync_method             | fdatasync
 work_mem                    | 16MB


9.2:
- ----

 checkpoint_segments         | 128
 client_encoding             | UTF8
 effective_cache_size        | 28892MB
 maintenance_work_mem        | 256MB
 max_connections             | 400
 max_stack_depth             | 4MB
 random_page_cost            | 2
 server_encoding             | UTF8
 shared_buffers              | 8026MB
 ssl                         | on
 ssl_renegotiation_limit     | 0
 wal_buffers                 | 16MB
 wal_level                   | archive
 wal_sync_method             | fdatasync
 work_mem                    | 16MB


Any ideas on why this is happening and how to fix it?

Thanks in advance for your time.
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)

iEYEARECAAYFAlIbSyoACgkQBhuKQurGihTOYwCfWC/ptAuMQ1pxFcplq9bHfBi3
uekAnj+nll/Z2Lr8kFgPAB6Fx0Kop4/0
=3TPA
-----END PGP SIGNATURE-----