Message-ID: <4FC80000.2000900@atlassian.com>
Date: Fri, 01 Jun 2012 09:34:24 +1000
From: Trevor Campbell <tcampbell@atlassian.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:12.0) Gecko/20120430 Thunderbird/12.0.1
MIME-Version: 1.0
To: Craig James <cjames@emolecules.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Trouble with plan statistics for behaviour for query.
References: <4FC7F0DB.40600@atlassian.com>
	<CAFwQ8rcEyUyhyUHrhQRzJk=DRVYhZT9-mJmvKAPoD8+8_NWG5g@mail.gmail.com>
	<4FC7F844.8070205@atlassian.com>
	<CAFwQ8rf89H+BCvua_EnjtPPK1onrxUA_mMDA8nr9hd5VgQvtcA@mail.gmail.com>
In-Reply-To: 
 <CAFwQ8rf89H+BCvua_EnjtPPK1onrxUA_mMDA8nr9hd5VgQvtcA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
>