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

Performance problems with prepared statements

From: Theo Kramer <theo(at)flame(dot)co(dot)za>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance problems with prepared statements
Date: 2007-10-10 14:45:40
Message-ID: 1192027540.2495.3.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-performance
Hi

I have been having some serious performance issues when using prepared
statements which I can not re-produce when using a direct statement. Let
me try to explain

The query does an order by in descending order on several columns for
which an index exists. 

The explain output as follows

rascal=# explain SELECT oid, * FROM calllog
WHERE calllog_mainteng = '124         '
AND calllog_phase = 8
AND calllog_self < 366942
OR calllog_mainteng = '124         '
AND calllog_phase < 8
ORDER BY calllog_mainteng DESC,
 calllog_phase DESC,
 calllog_self DESC limit 25;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..111.62 rows=25 width=2164)
   ->  Index Scan Backward using calllog_rmc_idx on calllog
(cost=0.00..53475.22 rows=11977 width=2164)
         Index Cond: (calllog_mainteng = '124         '::bpchar)
         Filter: (((calllog_phase = 8) AND (calllog_self < 366942)) OR
(calllog_phase < 8))

When running the query directly from psql it returns the required rows
in less than 100 milli-seconds.

However, when using a prepared statement from my C application on the
above query and executing it the query duration is as follows

SELECT oid, * FROM calllog
WHERE calllog_mainteng = '124         '
AND calllog_phase = 8
AND calllog_self < 366942
OR calllog_mainteng = '124         '
AND calllog_phase < 8
ORDER BY calllog_mainteng DESC,
 calllog_phase DESC,
 calllog_self DESC limit 25
Row[s] = 25, Duration = 435409.474 ms

The index as per the explain is defined as follows

"calllog_rmc_idx" UNIQUE, btree (calllog_mainteng, calllog_phase,
calllog_self)

VACUUM and all those good things done

Version of PostgreSQL 8.1 and 8.2

enable_seqscan = off
enable_sort = off

Any advice/suggestions/thoughts much appreciated

-- 
Regards
Theo


Responses

pgsql-performance by date

Next:From: Rodrigo De LeónDate: 2007-10-10 14:58:51
Subject: Re: SQL Monitoring
Previous:From: Kevin GrittnerDate: 2007-10-10 14:15:02
Subject: Re: hashjoin chosen over 1000x faster plan

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