Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 88F569F955C
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 10 Oct 2007 16:34:15 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 86474-10 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 10 Oct 2007 16:34:03 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from flame.co.za (rascal.co.za [160.124.170.1])
	by postgresql.org (Postfix) with ESMTP id A2F149F95C2
	for <pgsql-performance@postgresql.org>;
	Wed, 10 Oct 2007 16:34:06 -0300 (ADT)
Received: from [192.168.0.97] (vc-196-207-32-226.3g.vodacom.co.za
	[196.207.32.226]) (using TLSv1 with cipher RC4-MD5 (128/128 bits))
	(No client certificate requested)
	by flame.co.za (Postfix) with ESMTP id 5F9CE800CE
	for <pgsql-performance@postgresql.org>;
	Wed, 10 Oct 2007 21:34:01 +0200 (SAST)
Subject: Re: Performance problems with prepared statements
From: Theo Kramer <theo@flame.co.za>
Reply-To: theo@flame.co.za
To: pgsql-performance@postgresql.org
In-Reply-To: <470CE918.4060608@dalibo.com>
References: <1192027540.2495.3.camel@localhost.localdomain>
	<470CE918.4060608@dalibo.com>
Content-Type: text/plain; charset=UTF-8
Organization: Flame Computing Enterprises cc
Date: Wed, 10 Oct 2007 21:34:00 +0200
Message-Id: <1192044840.2549.21.camel@fedmac>
Mime-Version: 1.0
X-Mailer: Evolution 2.10.3 (2.10.3-4.fc7) 
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.946 tagged_above=0 required=5 tests=AWL=0.477,
	BAYES_05=-1.11, RCVD_IN_BL_SPAMCOP_NET=1.96, RCVD_IN_SORBS_WEB=0.619
X-Spam-Level: *
X-Archive-Number: 200710/105
X-Sequence-Number: 27127

On Wed, 2007-10-10 at 17:00 +0200, Cédric Villemain wrote:
> <snip>   
> Reading the manual, you can learn that prepared statement can (not) 
> follow the same plan as direct query:
> the plan is make before pg know the value of the variable.
> 
> See 'Notes' http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html

Thanks, had missed that, however, I am afraid that I fail to see how
preparing a query using PQprepare() and then executing it using
PQexecPrepared(), is 8 thousand times slower than directly executing
it.,, ( 403386.583ms/50.0ms =  8067 ).

When doing a 'manual' prepare and explain analyze I get the following

rascal=# prepare cq (char(12), smallint, integer) as SELECT oid,
calllog_mainteng, calllog_phase, calllog_self FROM calllog
WHERE calllog_mainteng = $1
AND calllog_phase = $2
AND calllog_self < $3    
OR calllog_mainteng = $1            
AND calllog_phase < $2
ORDER BY calllog_mainteng DESC,
 calllog_phase DESC,
 calllog_self DESC limit 25;
PREPARE
rascal=# explain analyze execute cq ('124         ', 8, 366942);
                                                                   QUERY
PLAN                                                                    
---------------------------------------------------------------------------
 Limit  (cost=0.00..232.73 rows=25 width=26) (actual time=2.992..3.178
rows=25 loops=1)
   ->  Index Scan Backward using calllog_rmc_idx on calllog
(cost=0.00..38651.38 rows=4152 width=26) (actual time=2.986..3.116
rows=25 loops=1)
         Index Cond: (calllog_mainteng = $1)
         Filter: (((calllog_phase = $2) AND (calllog_self < $3)) OR
(calllog_phase < $2))
 Total runtime: 3.272 ms


So I suspect that there is something more fundamental here...

-- 
Regards
Theo