From: Mario Splivalo <mario.splivalo@megafon.hr>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Different query plans on same servers
Date: Tue, 06 Dec 2011 20:48:10 +0100
Message-ID: <4EDE717A.9050604@megafon.hr> (raw)
I have a fairly simple query:
SELECT <some columns>
FROM "tubesite_image"
INNER JOIN "tubesite_object"
ON ("tubesite_image"."object_ptr_id" = "tubesite_object"."id")
WHERE
"tubesite_object"."site_id" = 8
ORDER BY
"tubesite_object"."pub_date" ASC LIMIT 21;
That query is having a bad query plan on production server:
Limit (cost=0.00..1938.67 rows=21 width=275) (actual
time=3270.000..3270.000 rows=0 loops=1)
-> Nested Loop (cost=0.00..792824.51 rows=8588 width=275) (actual
time=3269.997..3269.997 rows=0 loops=1)
-> Index Scan using tubesite_object_pub_date_idx on
tubesite_object (cost=0.00..789495.13 rows=9711 width=271) (actual
time=0.011..3243.629 rows=9905 loops=1)
Filter: (site_id = 8)
-> Index Scan using tubesite_image_pkey on tubesite_image
(cost=0.00..0.33 rows=1 width=4) (actual time=0.002..0.002 rows=0
loops=9905)
Index Cond: (tubesite_image.object_ptr_id =
tubesite_object.id)
Total runtime: 3270.071 ms
But, when I turn off nested loops, the query flies:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=31384.35..31384.40 rows=21 width=275) (actual
time=37.988..37.988 rows=0 loops=1)
-> Sort (cost=31384.35..31405.82 rows=8588 width=275) (actual
time=37.986..37.986 rows=0 loops=1)
Sort Key: tubesite_object.pub_date
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=857.00..31152.80 rows=8588 width=275)
(actual time=37.968..37.968 rows=0 loops=1)
Hash Cond: (tubesite_object.id =
tubesite_image.object_ptr_id)
-> Bitmap Heap Scan on tubesite_object
(cost=596.77..30685.30 rows=9711 width=271) (actual time=7.414..25.132
rows=9905 loops=1)
Recheck Cond: (site_id = 8)
-> Bitmap Index Scan on tubesite_object_site_id
(cost=0.00..594.34 rows=9711 width=0) (actual time=4.943..4.943
rows=9905 loops=1)
Index Cond: (site_id = 8)
-> Hash (cost=152.88..152.88 rows=8588 width=4) (actual
time=4.620..4.620 rows=8588 loops=1)
-> Seq Scan on tubesite_image (cost=0.00..152.88
rows=8588 width=4) (actual time=0.005..2.082 rows=8588 loops=1)
Total runtime: 38.071 ms
I have rsynced the database from the prod server to the test server,
that has same configuration (shared buffers, work mem, estimated cache
size, and so on), and there it chooses bitmap heap scan with hash join
without disabling the nested loops.
I have 8.4.8 on producion and 8.4.9 on test, could that explain the
difference in plans chosen?
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: mario.splivalo@megafon.hr
Subject: Re: Different query plans on same servers
In-Reply-To: <4EDE717A.9050604@megafon.hr>
* 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