Disparity between 8.1.18 and 8.2.14 performance wise

After many days of googling and referring to different web pages about perf=
ormance, I'm
turning to this list for help. We have a third party application that is ru=
nning on 8.1.11 and the
vendor has told us not to upgrade the database to 8.2.=20

I have gone with the default values in postgresql.conf of 8.1.18 (the 8.1.1=
1 was unavailable=20
from the postgresql site so this is the closest one) and 8.2.14. I execute =
the same query with=20
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=3D995.99..996.01 rows=3D1 width=3D66) (actual time=3D=
1042.850..1042.892 rows=3D21 loops=3D1)
-> Hash Join (cost=3D22.99..995.99 rows=3D1 width=3D66) (actual time=
=3D204.591..1042.745 rows=3D21 loops=3D1)
Hash Cond: ("outer".user_project_id =3D "inner".user_project_id)
-> Nested Loop (cost=3D2.03..974.97 rows=3D8 width=3D70) (actual=
time=3D6.976..969.505 rows=3D52202 loops=3D1)
-> Seq Scan on role_setting (cost=3D0.00..964.50 rows=3D1 =
width=3D70) (actual time=3D0.036..121.443 rows=3D43833 loops=3D1)
Filter: (((section)::text =3D (section)::text) AND (re=
f_id =3D ref_id))
-> Bitmap Heap Scan on user_project_role (cost=3D2.03..10.=
38 rows=3D8 width=3D8) (actual time=3D0.009..0.011 rows=3D1 loops=3D43833)
Recheck Cond: ("outer".role_id =3D user_project_role.r=
ole_id)
-> Bitmap Index Scan on userprojectrole_roleiduserpro=
jectid (cost=3D0.00..2.03 rows=3D8 width=3D0) (actual time=3D0.006..0.006 =
rows=3D1 loops=3D43833)
Index Cond: ("outer".role_id =3D user_project_ro=
le.role_id)
-> Hash (cost=3D20.96..20.96 rows=3D3 width=3D4) (actual time=3D=
0.365..0.365 rows=3D1 loops=3D1)
-> Bitmap Heap Scan on user_project (cost=3D11.99..20.96 r=
ows=3D3 width=3D4) (actual time=3D0.341..0.347 rows=3D1 loops=3D1)
Recheck Cond: (project_id =3D 67)
Filter: ((user_id =3D 102) OR (hashed subplan))
-> Bitmap Index Scan on user_project_pkey (cost=3D0.=
00..10.77 rows=3D7 width=3D0) (actual time=3D0.207..0.207 rows=3D6 loops=3D=
1)
Index Cond: (project_id =3D 67)
SubPlan
-> Seq Scan on usergroup_user (cost=3D0.00..1.21 r=
ows=3D1 width=3D4) (actual time=3D0.020..0.036 rows=3D1 loops=3D1)
Filter: (member_user_id =3D 102)
Total runtime: 1043.493 ms


HashAggregate (cost=3D77.51..77.52 rows=3D1 width=3D20) (actual time=3D6.=
172..6.217 rows=3D21 loops=3D1)
-> Nested Loop (cost=3D34.15..77.50 rows=3D1 width=3D20) (actual time=
=3D1.972..6.106 rows=3D21 loops=3D1)
-> Hash Join (cost=3D34.15..64.18 rows=3D6 width=3D4) (actual ti=
me=3D1.884..5.847 rows=3D1 loops=3D1)
Hash Cond: (user_project_role.user_project_id =3D user_proje=
ct.user_project_id)
-> Seq Scan on user_project_role (cost=3D0.00..23.98 rows=
=3D1598 width=3D8) (actual time=3D0.028..2.349 rows=3D1598 loops=3D1)
-> Hash (cost=3D34.09..34.09 rows=3D5 width=3D4) (actual t=
ime=3D0.752..0.752 rows=3D1 loops=3D1)
-> Seq Scan on user_project (cost=3D1.21..34.09 rows=
=3D5 width=3D4) (actual time=3D0.327..0.744 rows=3D1 loops=3D1)
Filter: ((project_id =3D 67) AND ((user_id =3D 1=
02) OR (hashed subplan)))
SubPlan
-> Seq Scan on usergroup_user (cost=3D0.00..=
1.21 rows=3D1 width=3D4) (actual time=3D0.050..0.056 rows=3D1 loops=3D1)
Filter: (member_user_id =3D 102)
-> Index Scan using role_setting_pkey on role_setting (cost=3D0.=
00..2.21 rows=3D1 width=3D24) (actual time=3D0.081..0.194 rows=3D21 loops=
=3D1)
Index Cond: (role_setting.role_id =3D user_project_role.role=
_id)
Filter: (((section)::text =3D (section)::text) AND (ref_id =
=3D ref_id))
Total runtime: 6.905 ms


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disparity between 8.1.18 and 8.2.14 performance wise

On Mon, Mar 22, 2010 at 3:58 PM, Dai, Tino wrote:
> After many days of googling and referring to different web pages about pe=
rformance, 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 execut=
e the same query with
> EXPLAIN ANALYZE on 8.1.18 and the 8.2.14. The 8.1.18 comes back with abou=
t 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_buffer=
s with no noticeable effect.
> I'm not looking for a magic wand to allow 8.1.18 to become 8.2.14, but so=
me performance gain would be
> nice. Can any shed some light on this?

Why?

> =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (((section)::text =3D (section)::text=
) AND (ref_id =3D ref_id))
> =A0Total runtime: 6.905 ms

Is this the result from 8.2? Cause it's not 6 seconds, it's 6 milliseconds=
...

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disparity between 8.1.18 and 8.2.14 performance wise

"Dai, Tino" 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disparity between 8.1.18 and 8.2.14 performance wise

--=-w2x6IlPpywMkCOiRcfzQ
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

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
To: 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.



--=-w2x6IlPpywMkCOiRcfzQ
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit








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 <>

To: 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. 

Re: Disparity between 8.1.18 and 8.2.14 performance wise

> 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.=20

>> 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=3D0.00..964.50 rows=
=3D1 width=3D70) (actual time=3D0.036..121.443 rows=3D43833 loops=3D1)
>> Filter: (((section)::text =3D (section)::text) AND =
(ref_id =3D 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 too=
l=20
that can be put in front of postgres?=20

Thanks,
Tino


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Disparity between 8.1.18 and 8.2.14 performance wise

"Dai, Tino" 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin