pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDisparity between 8.1.18 and 8.2.14 performance wise
6+ messages / 4 participants
[nested] [flat]
* Disparity between 8.1.18 and 8.2.14 performance wise
@ 2010-03-22 21:58 Dai, Tino <tdai@loc.gov>
2010-03-22 22:32 ` Re: Disparity between 8.1.18 and 8.2.14 performance wise Scott Marlowe <scott.marlowe@gmail.com>
2010-03-22 23:07 ` Re: Disparity between 8.1.18 and 8.2.14 performance wise Tom Lane <tgl@sss.pgh.pa.us>
2010-03-23 08:14 ` Re: Disparity between 8.1.18 and 8.2.14 performance wise Iñigo Martinez Lasala <imartinez@vectorsf.com>
0 siblings, 3 replies; 6+ messages in thread
From: Dai, Tino @ 2010-03-22 21:58 UTC (permalink / raw)
To: pgsql-admin
After many days of googling and referring to different web pages about performance, I'm
turning to this list for help. We have a third party application that is running on 8.1.11 and the
vendor has told us not to upgrade the database to 8.2.
I have gone with the default values in postgresql.conf of 8.1.18 (the 8.1.11 was unavailable
from the postgresql site so this is the closest one) and 8.2.14. I execute the same query with
EXPLAIN ANALYZE on 8.1.18 and the 8.2.14. The 8.1.18 comes back with about 1000ms -1100ms
while the 8.2.14 comes back with 5ms - 6ms.
I have tried up the work_mem, effective_cache_size, and the shared_buffers with no noticeable effect.
I'm not looking for a magic wand to allow 8.1.18 to become 8.2.14, but some performance gain would be
nice. Can any shed some light on this?
Thanks,
Tino
HashAggregate (cost=995.99..996.01 rows=1 width=66) (actual time=1042.850..1042.892 rows=21 loops=1)
-> Hash Join (cost=22.99..995.99 rows=1 width=66) (actual time=204.591..1042.745 rows=21 loops=1)
Hash Cond: ("outer".user_project_id = "inner".user_project_id)
-> Nested Loop (cost=2.03..974.97 rows=8 width=70) (actual time=6.976..969.505 rows=52202 loops=1)
-> Seq Scan on role_setting (cost=0.00..964.50 rows=1 width=70) (actual time=0.036..121.443 rows=43833 loops=1)
Filter: (((section)::text = (section)::text) AND (ref_id = ref_id))
-> Bitmap Heap Scan on user_project_role (cost=2.03..10.38 rows=8 width=8) (actual time=0.009..0.011 rows=1 loops=43833)
Recheck Cond: ("outer".role_id = user_project_role.role_id)
-> Bitmap Index Scan on userprojectrole_roleiduserprojectid (cost=0.00..2.03 rows=8 width=0) (actual time=0.006..0.006 rows=1 loops=43833)
Index Cond: ("outer".role_id = user_project_role.role_id)
-> Hash (cost=20.96..20.96 rows=3 width=4) (actual time=0.365..0.365 rows=1 loops=1)
-> Bitmap Heap Scan on user_project (cost=11.99..20.96 rows=3 width=4) (actual time=0.341..0.347 rows=1 loops=1)
Recheck Cond: (project_id = 67)
Filter: ((user_id = 102) OR (hashed subplan))
-> Bitmap Index Scan on user_project_pkey (cost=0.00..10.77 rows=7 width=0) (actual time=0.207..0.207 rows=6 loops=1)
Index Cond: (project_id = 67)
SubPlan
-> Seq Scan on usergroup_user (cost=0.00..1.21 rows=1 width=4) (actual time=0.020..0.036 rows=1 loops=1)
Filter: (member_user_id = 102)
Total runtime: 1043.493 ms
HashAggregate (cost=77.51..77.52 rows=1 width=20) (actual time=6.172..6.217 rows=21 loops=1)
-> Nested Loop (cost=34.15..77.50 rows=1 width=20) (actual time=1.972..6.106 rows=21 loops=1)
-> Hash Join (cost=34.15..64.18 rows=6 width=4) (actual time=1.884..5.847 rows=1 loops=1)
Hash Cond: (user_project_role.user_project_id = user_project.user_project_id)
-> Seq Scan on user_project_role (cost=0.00..23.98 rows=1598 width=8) (actual time=0.028..2.349 rows=1598 loops=1)
-> Hash (cost=34.09..34.09 rows=5 width=4) (actual time=0.752..0.752 rows=1 loops=1)
-> Seq Scan on user_project (cost=1.21..34.09 rows=5 width=4) (actual time=0.327..0.744 rows=1 loops=1)
Filter: ((project_id = 67) AND ((user_id = 102) OR (hashed subplan)))
SubPlan
-> Seq Scan on usergroup_user (cost=0.00..1.21 rows=1 width=4) (actual time=0.050..0.056 rows=1 loops=1)
Filter: (member_user_id = 102)
-> Index Scan using role_setting_pkey on role_setting (cost=0.00..2.21 rows=1 width=24) (actual time=0.081..0.194 rows=21 loops=1)
Index Cond: (role_setting.role_id = user_project_role.role_id)
Filter: (((section)::text = (section)::text) AND (ref_id = ref_id))
Total runtime: 6.905 ms
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disparity between 8.1.18 and 8.2.14 performance wise
2010-03-22 21:58 Disparity between 8.1.18 and 8.2.14 performance wise Dai, Tino <tdai@loc.gov>
@ 2010-03-22 22:32 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2010-03-22 22:32 UTC (permalink / raw)
To: Dai, Tino <tdai@loc.gov>; +Cc: pgsql-admin
On Mon, Mar 22, 2010 at 3:58 PM, Dai, Tino <tdai@loc.gov> wrote:
> After many days of googling and referring to different web pages about performance, I'm
> turning to this list for help. We have a third party application that is running on 8.1.11 and the
> vendor has told us not to upgrade the database to 8.2.
>
> I have gone with the default values in postgresql.conf of 8.1.18 (the 8.1.11 was unavailable
> from the postgresql site so this is the closest one) and 8.2.14. I execute the same query with
> EXPLAIN ANALYZE on 8.1.18 and the 8.2.14. The 8.1.18 comes back with about 1000ms -1100ms
> while the 8.2.14 comes back with 5ms - 6ms.
That's a factor of about 200 times faster for 8.2.
> I have tried up the work_mem, effective_cache_size, and the shared_buffers with no noticeable effect.
> I'm not looking for a magic wand to allow 8.1.18 to become 8.2.14, but some performance gain would be
> nice. Can any shed some light on this?
Why?
> Filter: (((section)::text = (section)::text) AND (ref_id = ref_id))
> Total runtime: 6.905 ms
Is this the result from 8.2? Cause it's not 6 seconds, it's 6 milliseconds..
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disparity between 8.1.18 and 8.2.14 performance wise
2010-03-22 21:58 Disparity between 8.1.18 and 8.2.14 performance wise Dai, Tino <tdai@loc.gov>
@ 2010-03-22 23:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-23 15:22 ` Re: Disparity between 8.1.18 and 8.2.14 performance wise Dai, Tino <tdai@loc.gov>
2 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2010-03-22 23:07 UTC (permalink / raw)
To: Dai, Tino <tdai@loc.gov>; +Cc: pgsql-admin
"Dai, Tino" <tdai@loc.gov> writes:
> After many days of googling and referring to different web pages about performance, I'm
> turning to this list for help. We have a third party application that is running on 8.1.11 and the
> vendor has told us not to upgrade the database to 8.2.
8.2 was released in 2006. 8.1 is going to be desupported entirely at
the end of 2010. You really need to be holding your vendor's feet to
the fire about supporting modern versions of Postgres, rather than
looking for workarounds.
But having said that, I think 8.1 might generate a reasonable plan if it
weren't getting misled by these useless constraints:
> -> Seq Scan on role_setting (cost=0.00..964.50 rows=1 width=70) (actual time=0.036..121.443 rows=43833 loops=1)
> Filter: (((section)::text = (section)::text) AND (ref_id = ref_id))
Can you get rid of those?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disparity between 8.1.18 and 8.2.14 performance wise
2010-03-22 21:58 Disparity between 8.1.18 and 8.2.14 performance wise Dai, Tino <tdai@loc.gov>
2010-03-22 23:07 ` Re: Disparity between 8.1.18 and 8.2.14 performance wise Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-03-23 15:22 ` Dai, Tino <tdai@loc.gov>
2010-03-23 16:26 ` Re: Disparity between 8.1.18 and 8.2.14 performance wise Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Dai, Tino @ 2010-03-23 15:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> 8.2 was released in 2006. 8.1 is going to be desupported entirely at
> the end of 2010. You really need to be holding your vendor's feet to
> the fire about supporting modern versions of Postgres, rather than
> looking for workarounds.
I think that is the correct move.
>> But having said that, I think 8.1 might generate a reasonable plan if it
>> weren't getting misled by these useless constraints:
>> -> Seq Scan on role_setting (cost=0.00..964.50 rows=1 width=70) (actual time=0.036..121.443 rows=43833 loops=1)
>> Filter: (((section)::text = (section)::text) AND (ref_id = ref_id))
> Can you get rid of those?
Unfortunately, I can't. The third-party product is protected by some kind of
obfuscation program. :( But is there any kind of external query rewrite tool
that can be put in front of postgres?
Thanks,
Tino
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disparity between 8.1.18 and 8.2.14 performance wise
2010-03-22 21:58 Disparity between 8.1.18 and 8.2.14 performance wise Dai, Tino <tdai@loc.gov>
2010-03-22 23:07 ` Re: Disparity between 8.1.18 and 8.2.14 performance wise Tom Lane <tgl@sss.pgh.pa.us>
2010-03-23 15:22 ` Re: Disparity between 8.1.18 and 8.2.14 performance wise Dai, Tino <tdai@loc.gov>
@ 2010-03-23 16:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2010-03-23 16:26 UTC (permalink / raw)
To: Dai, Tino <tdai@loc.gov>; +Cc: pgsql-admin
"Dai, Tino" <tdai@loc.gov> writes:
>>> But having said that, I think 8.1 might generate a reasonable plan if it
>>> weren't getting misled by these useless constraints:
>>> -> Seq Scan on role_setting (cost=0.00..964.50 rows=1 width=70) (actual time=0.036..121.443 rows=43833 loops=1)
>>> Filter: (((section)::text = (section)::text) AND (ref_id = ref_id))
>> Can you get rid of those?
> Unfortunately, I can't. The third-party product is protected by some kind of
> obfuscation program. :( But is there any kind of external query rewrite tool
> that can be put in front of postgres?
Can't think of anything that would be useful for that. But you could
possibly modify eqsel() so that it checks for the two inputs being
equal() and returns a more reasonable selectivity for that case.
We don't do that by default because it'd usually be a waste of cycles;
but if you're dealing with an application that likes to generate such
clauses, it'd be worth your time.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Disparity between 8.1.18 and 8.2.14 performance wise
2010-03-22 21:58 Disparity between 8.1.18 and 8.2.14 performance wise Dai, Tino <tdai@loc.gov>
@ 2010-03-23 08:14 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
2 siblings, 0 replies; 6+ messages in thread
From: Iñigo Martinez Lasala @ 2010-03-23 08:14 UTC (permalink / raw)
To: Dai, Tino <tdai@loc.gov>; +Cc: pgsql-admin
We had never problems when migrating from 8.1 to 8.2
Problems appear if you migrate to 8.3 or higher (due to explicit
conversions in data types and tsearch changes). But moving from 8.1 to
8.2 should be really easy and shouldn't suppose a problem.
-----Original Message-----
From: Dai, Tino <tdai@loc.gov>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: [ADMIN] Disparity between 8.1.18 and 8.2.14 performance wise
Date: Mon, 22 Mar 2010 17:58:41 -0400
After many days of googling and referring to different web pages about performance, I'm
turning to this list for help. We have a third party application that is running on 8.1.11 and the
vendor has told us not to upgrade the database to 8.2.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-03-23 16:26 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-22 21:58 Disparity between 8.1.18 and 8.2.14 performance wise Dai, Tino <tdai@loc.gov>
2010-03-22 22:32 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-03-22 23:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-23 15:22 ` Dai, Tino <tdai@loc.gov>
2010-03-23 16:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-23 08:14 ` Iñigo Martinez Lasala <imartinez@vectorsf.com>
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