Re: Performance problems with prepared statements

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

               http://www.postgresql.org/about/donate

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux