From: | Theo Kramer <theo(at)flame(dot)co(dot)za> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Performance problems with prepared statements |
Date: | 2007-10-10 19:34:00 |
Message-ID: | 1192044840.2549.21.camel@fedmac |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2007-10-10 19:35:58 | Re: hashjoin chosen over 1000x faster plan |
Previous Message | Simon Riggs | 2007-10-10 19:01:03 | Re: hashjoin chosen over 1000x faster plan |