From: Mario Splivalo <mario.splivalo@megafon.hr>
To: pgsql-performance@postgresql.org
Subject: Re: Different query plans on same servers
Date: Wed, 07 Dec 2011 01:35:18 +0100
Message-ID: <4EDEB4C6.2070207@megafon.hr> (raw)
In-Reply-To: <4EDE26AF0200002500043945@gw.wicourts.gov>
References: <4EDE717A.9050604@megafon.hr>
<4EDE717A.9050604@megafon.hr>
<14289.1323201649@sss.pgh.pa.us>
<4EDE23FA020000250004393F@gw.wicourts.gov>
<4EDE26AF0200002500043945@gw.wicourts.gov>
On 12/06/2011 09:29 PM, Kevin Grittner wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
>
>> But both servers develop that estimate for the join size.
>
> [sigh] Those *were* both from the production server. Please show
> us the EXPLAIN ANALYZE from the other server.
Huh, right... missed that one. Here is the 'explain analyze' from the
other server:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=31531.75..31531.80 rows=21 width=275) (actual
time=45.584..45.584 rows=0 loops=1)
-> Sort (cost=31531.75..31531.84 rows=36 width=275) (actual
time=45.579..45.579 rows=0 loops=1)
Sort Key: tubesite_object.pub_date
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=866.34..31530.82 rows=36 width=275)
(actual time=45.544..45.544 rows=0 loops=1)
Hash Cond: (tubesite_object.id =
tubesite_image.object_ptr_id)
-> Bitmap Heap Scan on tubesite_object
(cost=606.11..31146.68 rows=9884 width=271) (actual time=6.861..37.497
rows=9905 loops=1)
Recheck Cond: (site_id = 8)
-> Bitmap Index Scan on tubesite_object_site_id
(cost=0.00..603.64 rows=9884 width=0) (actual time=4.792..4.792
rows=9905 loops=1)
Index Cond: (site_id = 8)
-> Hash (cost=152.88..152.88 rows=8588 width=4) (actual
time=3.816..3.816 rows=8588 loops=1)
-> Seq Scan on tubesite_image (cost=0.00..152.88
rows=8588 width=4) (actual time=0.003..1.740 rows=8588 loops=1)
Total runtime: 45.798 ms
This is also a query from the prod server, but without LIMIT:
----------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=31713.95..31735.42 rows=8588 width=275) (actual
time=60.311..60.311 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=60.255..60.255 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=8.682..49.721 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=5.705..5.705
rows=9905 loops=1)
Index Cond: (site_id = 8)
-> Hash (cost=152.88..152.88 rows=8588 width=4) (actual
time=4.281..4.281 rows=8588 loops=1)
-> Seq Scan on tubesite_image (cost=0.00..152.88
rows=8588 width=4) (actual time=0.005..1.437 rows=8588 loops=1)
Total runtime: 60.483 ms
(12 rows)
I will try to rsync prod database to 8.4.8 on test server tomorrow, and
see what happens. Hopefully upgrade to 8.4.9 (or even 8.4.10 if Debian
packages is by tomorrow) will solve the issue...
Mario
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: <4EDEB4C6.2070207@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