From: Rafael Martinez <r.m.guerrero@usit.uio.no>
To: pgsql-performance@postgresql.org
Subject: SQL statement over 500% slower with 9.2 compared with 9.1
Date: Mon, 26 Aug 2013 14:33:46 +0200
Message-ID: <521B4B2A.5010800@usit.uio.no> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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-----
--
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
Subject: Re: SQL statement over 500% slower with 9.2 compared with 9.1
In-Reply-To: <521B4B2A.5010800@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