Обсуждение: Disparity between 8.1.18 and 8.2.14 performance wise

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=1loops=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)
(actualtime=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=1loops=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.207rows=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=1loops=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=1598loops=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=1loops=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.194rows=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

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

"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=43833loops=1) 
>                      Filter: (((section)::text = (section)::text) AND (ref_id = ref_id))

Can you get rid of those?

            regards, tom lane

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. 

> 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=43833loops=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

"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