From: Trevor Campbell <tcampbell@atlassian.com>
To: Craig James <cjames@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Trouble with plan statistics for behaviour for query.
Date: Fri, 01 Jun 2012 09:34:24 +1000
Message-ID: <4FC80000.2000900@atlassian.com> (raw)
In-Reply-To: <CAFwQ8rf89H+BCvua_EnjtPPK1onrxUA_mMDA8nr9hd5VgQvtcA@mail.gmail.com>
References: <4FC7F0DB.40600@atlassian.com>
<CAFwQ8rcEyUyhyUHrhQRzJk=DRVYhZT9-mJmvKAPoD8+8_NWG5g@mail.gmail.com>
<4FC7F844.8070205@atlassian.com>
<CAFwQ8rf89H+BCvua_EnjtPPK1onrxUA_mMDA8nr9hd5VgQvtcA@mail.gmail.com>
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
>
>
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: tcampbell@atlassian.com, cjames@emolecules.com
Subject: Re: Trouble with plan statistics for behaviour for query.
In-Reply-To: <4FC80000.2000900@atlassian.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