Received: from localhost (unknown [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 4B5F69FA5E4
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 11 Oct 2007 05:52:12 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 47832-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 11 Oct 2007 05:52:07 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from dalibo-free-72.custom.lost-oasis.net (unknown [212.85.154.72])
	by postgresql.org (Postfix) with ESMTP id B32489F9BCF
	for <pgsql-performance@postgresql.org>;
	Thu, 11 Oct 2007 05:52:07 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by dalibo-free-72.custom.lost-oasis.net (Postfix) with ESMTP id
	6D34B578076; Thu, 11 Oct 2007 10:52:06 +0200 (CEST)
Received: from dalibo-free-72.custom.lost-oasis.net ([127.0.0.1])
	by localhost (pyrrha.dalibo.net [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id U++2ijHAIACK; Thu, 11 Oct 2007 10:52:06 +0200 (CEST)
Received: by dalibo-free-72.custom.lost-oasis.net (Postfix, from userid 1011)
	id 4B342578078; Thu, 11 Oct 2007 10:52:06 +0200 (CEST)
Received: from [192.168.0.12] (lan31-6-82-238-146-117.fbx.proxad.net
	[82.238.146.117])
	by dalibo-free-72.custom.lost-oasis.net (Postfix) with ESMTP id
	DEB90578076; Thu, 11 Oct 2007 10:52:03 +0200 (CEST)
Message-ID: <470DE42E.1060100@dalibo.com>
Date: Thu, 11 Oct 2007 10:51:58 +0200
From: =?UTF-8?B?Q8OpZHJpYyBWaWxsZW1haW4=?= <cedric.villemain@dalibo.com>
User-Agent: Mozilla-Thunderbird 2.0.0.4 (X11/20070828)
MIME-Version: 1.0
To: theo@flame.co.za
CC: pgsql-performance@postgresql.org
Subject: Re: Performance problems with prepared statements
References: <1192027540.2495.3.camel@localhost.localdomain>	
	<470CE918.4060608@dalibo.com> <1192044840.2549.21.camel@fedmac>
In-Reply-To: <1192044840.2549.21.camel@fedmac>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200710/123
X-Sequence-Number: 27145

Theo Kramer a écrit :
> 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...
>   
my two cents:
perhaps ... please check that with your C code
And be sure you are not providing time from application. If you have a 
lot of data and/or  a lag  on your lan, it can be the cause of your so 
big difference between psql and C