If I am correct, JDBC uses named portal only on the 5th time you use
PreparedStatement (configurable). Before it uses unnamed thing that
should work as if you did embed the value. So the solution is to
recreate PreparedStatement each time (so you will have no problems with
SQL injection). Note that "smart" pools may detect this situation and
reuse PreparedStatement for same query texts internally. If so, this to
switch this off.
In case you still have problems, I'd recommend you to ask in postgresql
jdbc mailing list.
Also I've heard that somewhere in 9.2 postgresql server may replan such
cases each time.
Best regards, Vitalii Tymchyshyn
01.06.12 02:34, Trevor Campbell написав(ла):
Thanks Craig, that certainly leads down the right path.
The following is all done in pgAdmin3:
Using an actual value we I get the plan I expect
explain analyze select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED,
CI.ID, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE,
CI.NEWSTRING
from PUBLIC.CHANGEGROUP CG inner join PUBLIC.CHANGEITEM CI on CG.ID =
CI.GROUPID where CG.ISSUEID=10006 order by CG.CREATED asc, CI.ID asc
"Sort (cost=106.18..106.22 rows=13 width=434) (actual
time=0.115..0.115 rows=12 loops=1)"
" Sort Key: cg.created, ci.id"
" Sort Method: quicksort Memory: 29kB"
" -> Nested Loop (cost=0.00..105.94 rows=13 width=434) (actual
time=0.019..0.067 rows=12 loops=1)"
" -> Index Scan using chggroup_issue on changegroup cg
(cost=0.00..19.73 rows=10 width=29) (actual time=0.009..0.013 rows=10
loops=1)"
" Index Cond: (issueid = 10006::numeric)"
" -> Index Scan using chgitem_chggrp on changeitem ci (cost=0.00..8.58
rows=3 width=411) (actual time=0.004..0.005 rows=1 loops=10)"
" Index Cond: (groupid = cg.id)"
"Total runtime: 0.153 ms"
Using a prepared statement with a variable , I get a poor plan
requiring a sequential scan
prepare t2(real) as
select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, CI.FIELDTYPE,
CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING
from PUBLIC.CHANGEGROUP CG inner join PUBLIC.CHANGEITEM CI on CG.ID =
CI.GROUPID where CG.ISSUEID=$1 order by CG.CREATED asc, CI.ID asc;
explain analyze execute t2 (10006);
"Sort (cost=126448.89..126481.10 rows=12886 width=434) (actual
time=1335.615..1335.616 rows=12 loops=1)"
" Sort Key: cg.created, ci.id"
" Sort Method: quicksort Memory: 29kB"
" -> Nested Loop (cost=0.00..125569.19 rows=12886 width=434) (actual
time=0.046..1335.556 rows=12 loops=1)"
" -> Seq Scan on changegroup cg (cost=0.00..44709.26 rows=10001
width=29) (actual time=0.026..1335.460 rows=10 loops=1)"
" Filter: ((issueid)::double precision = $1)"
" -> Index Scan using chgitem_chggrp on changeitem ci (cost=0.00..8.05
rows=3 width=411) (actual time=0.007..0.008 rows=1 loops=10)"
" Index Cond: (groupid = cg.id)"
"Total runtime: 1335.669 ms"
Using a prepared statement with a cast of the variable to the right
type, I get the good plan back
prepare t2(real) as
select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, CI.FIELDTYPE,
CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING
from PUBLIC.CHANGEGROUP CG inner join PUBLIC.CHANGEITEM CI on CG.ID =
CI.GROUPID where CG.ISSUEID=cast($1 as numeric) order by CG.CREATED
asc, CI.ID asc;
explain analyze execute t2 (10006);
"Sort (cost=106.19..106.22 rows=13 width=434) (actual
time=0.155..0.156 rows=12 loops=1)"
" Sort Key: cg.created, ci.id"
" Sort Method: quicksort Memory: 29kB"
" -> Nested Loop (cost=0.00..105.95 rows=13 width=434) (actual
time=0.048..0.111 rows=12 loops=1)"
" -> Index Scan using chggroup_issue on changegroup cg
(cost=0.00..19.73 rows=10 width=29) (actual time=0.031..0.042 rows=10
loops=1)"
" Index Cond: (issueid = ($1)::numeric)"
" -> Index Scan using chgitem_chggrp on changeitem ci (cost=0.00..8.58
rows=3 width=411) (actual time=0.006..0.006 rows=1 loops=10)"
" Index Cond: (groupid = cg.id)"
"Total runtime: 0.203 ms"
Now the challenge is to get java/jdbc to get this done right. We make
a big effort to ensure we always use prepared statements and variable
bindings to help protect from SQL injection vulnerabilities.
On 01/06/12 09:08, Craig James wrote:
I use Perl, not JDBC, but this thread may be relevant to your problem.
http://postgresql.1045698.n5.nabble.com/Slow-statement-when-using-JDBC-td3368379.html
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
