From: Cédric Villemain <cedric.villemain@dalibo.com>
To: theo@flame.co.za
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems with prepared statements
Date: Thu, 11 Oct 2007 10:51:58 +0200
Message-ID: <470DE42E.1060100@dalibo.com> (raw)
In-Reply-To: <1192044840.2549.21.camel@fedmac>
References: <1192027540.2495.3.camel@localhost.localdomain>
<470CE918.4060608@dalibo.com>
<1192044840.2549.21.camel@fedmac>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cedric.villemain@dalibo.com, theo@flame.co.za
Subject: Re: Performance problems with prepared statements
In-Reply-To: <470DE42E.1060100@dalibo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox