Skip site navigation (1) Skip section navigation (2)

Bad prepare performance

From: Martin Kjeldsen <martin(at)martinkjeldsen(dot)dk>
To: pgsql-performance(at)postgresql(dot)org
Subject: Bad prepare performance
Date: 2008-03-31 11:00:13
Message-ID: 20080331110013.GA6965@martinkjeldsen.dk (view raw or flat)
Thread:
Lists: pgsql-performance
Hello,

I'm sorry if this has been discussed before, I couldn't find a discussion about 
this problem.

I've done the same query on a 8.2.5 database. The first one is prepared first 
and the other is executed directly.

I understand why the there is such a great difference between the two ways of 
executing the query (postgres has no way of knowing that $1 will be quite big 
and that the result is not too big). 

I could just avoid using prepare statements, but this is done automatically with 
Perl's DBD::Pg. I know how to avoid using prepare statements (avoid having 
placeholders in the statement), but that is not the prettiest of work arounds.  
Is there any planner hints I can use or anything happened or happening with 8.3 
or later that I can use?

Thank you in advance and the following is the EXPLAIN ANALYZE for the queries.

Best regards


Martin Kjeldsen

-----

PREPARE test_x (INT) AS SELECT * FROM v_rt_trap_detailed WHERE guid > $1 ORDER BY created LIMIT 3000;

EXPLAIN ANALYZE EXECUTE test_x (116505531);
                                                                                      QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1143870.95..1143878.45 rows=3000 width=267) (actual time=83033.101..83033.111 rows=4 loops=1)
   ->  Sort  (cost=1143870.95..1148074.36 rows=1681367 width=267) (actual time=83033.099..83033.103 rows=4 loops=1)
         Sort Key: rt_trap.created
         ->  Merge Left Join  (cost=0.00..829618.73 rows=1681367 width=267) (actual time=83032.946..83033.051 rows=4 loops=1)
               Merge Cond: (rt_trap.guid = tp.trap_guid)
               ->  Index Scan using idx_rt_trap_guid on rt_trap  (cost=0.00..81738.88 rows=1681367 width=192) (actual time=0.012..0.020 rows=4 loops=1)
                     Index Cond: (guid > $1)
                     Filter: (deleted IS NULL)
               ->  Index Scan using idx_rt_trap_param_trap_guid on rt_trap_param tp  (cost=0.00..706147.04 rows=4992440 width=79) (actual time=6.523..78594.750 rows=5044927 loops=1)
                     Filter: (param_oid = 'snmpTrapOID.0'::text)
 Total runtime: 83033.411 ms
(11 rows)

dmon2=# EXPLAIN ANALYZE SELECT * FROM v_rt_trap_detailed WHERE guid > 116505531 ORDER BY created LIMIT 3000;
                                                                             QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=9866.45..9867.71 rows=504 width=267) (actual time=0.590..0.616 rows=12 loops=1)
   ->  Sort  (cost=9866.45..9867.71 rows=504 width=267) (actual time=0.587..0.596 rows=12 loops=1)
         Sort Key: rt_trap.created
         ->  Nested Loop Left Join  (cost=0.00..9843.83 rows=504 width=267) (actual time=0.157..0.531 rows=12 loops=1)
               ->  Index Scan using idx_rt_trap_guid on rt_trap  (cost=0.00..26.78 rows=504 width=192) (actual time=0.022..0.034 rows=12 loops=1)
                     Index Cond: (guid > 116505531)
                     Filter: (deleted IS NULL)
               ->  Index Scan using idx_rt_trap_param_trap_guid on rt_trap_param tp  (cost=0.00..18.36 rows=89 width=79) (actual time=0.006..0.009 rows=1 loops=12)
                     Index Cond: (rt_trap.guid = tp.trap_guid)
                     Filter: (param_oid = 'snmpTrapOID.0'::text)
 Total runtime: 0.733 ms
(11 rows)

Responses

pgsql-performance by date

Next:From: Frits HooglandDate: 2008-03-31 11:57:08
Subject: optimizing query performance
Previous:From: Guy RouillierDate: 2008-03-31 02:25:36
Subject: Re: Planning a new server - help needed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group