Subject: Performance problems with prepared statements
From: Theo Kramer <theo@flame.co.za>
Reply-To: theo@flame.co.za
To: pgsql-performance@postgresql.org
Content-Type: text/plain
Organization: Flame Computing Enterprises cc
Date: Wed, 10 Oct 2007 16:45:40 +0200
Message-Id: <1192027540.2495.3.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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