From: Tom Lane <tgl@sss.pgh.pa.us>
To: Aaron Guyon <battlemage@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres 8.3, four times slower queries?
Date: Mon, 02 Mar 2009 22:23:52 -0500
Message-ID: <2870.1236050632@sss.pgh.pa.us> (raw)
In-Reply-To: <f0afbb9b0903021618w616f1158t84dbf119f0b72c70@mail.gmail.com>
References: <f0afbb9b0903021618w616f1158t84dbf119f0b72c70@mail.gmail.com>
Aaron Guyon <battlemage@gmail.com> writes:
> We are currently running postgres 8.2 and are evaluating the upgrade to 8.3.
> Some of our tests are indicating that postgresql 8.3 is actually degrading
> the performance of some of our queries by a factor of 10 or more.
Are you sure you are comparing apples to apples here? Same configure
options for the builds, same parameter values in postgresql.conf, both
databases ANALYZEd, etc? And are they running on the same hardware?
The rowcount estimates seem to be a bit different, which might account
for the difference in plan choices, but I'm not convinced that that is
the reason for the slowness. The parts of the plans that are exactly
comparable show very significant speed differences, eg
> -> Index Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=24) (actual time=2.484..2.486 rows=1 loops=7)
> Index Cond: (t2.day_part_id = t10.id)
> Filter: (t2.active <> 0::numeric)
> -> Index Scan using idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.54 rows=92 width=25) (actual time=12.726..276.412 rows=94 loops=4)
> Index Cond: (t11.skin_id = t2.id)
> Filter: (t11.active <> 0::numeric)
> -> Index Scan using idx_skin_day_part_id on skin t2 (cost=0.00..6.28 rows=1 width=30) (actual time=0.028..0.031 rows=1 loops=7)
> Index Cond: (t2.day_part_id = t10.id)
> Filter: (active <> 0::numeric)
> -> Index Scan using idx_skin_slot_skin_id on skin_slot t11 (cost=0.00..6.85 rows=93 width=30) (actual time=0.053..1.382 rows=94 loops=4)
> Index Cond: (t2.id = t11.skin_id)
> Filter: (active <> 0::numeric)
There's nothing in 8.3 vs 8.2 to explain that, if they're configured
the same and running in the same environment.
regards, tom lane
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: tgl@sss.pgh.pa.us, battlemage@gmail.com
Subject: Re: Postgres 8.3, four times slower queries?
In-Reply-To: <2870.1236050632@sss.pgh.pa.us>
* 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