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