Re: Slow statement when using JDBC

I apologize I didn't paste the original query. The original query is more
complex in fact. Please, let me explain further:

select
DISTINCT
EVENT.ID, ORIGIN.ID AS ORIGINID,EVENT.PREFERRED_ORIGIN_ID AS
PREFERRED_ORIGIN,
EVENT.CONTRIBUTOR, ORIGIN.TIME, ORIGIN.LATITUDE, ORIGIN.LONGITUDE,
ORIGIN.DEPTH,ORIGIN.EVTYPE, "
ORIGIN.CATALOG, ORIGIN.AUTHOR OAUTHOR, ORIGIN.CONTRIBUTOR OCONTRIBUTOR,
MAGNITUDE.ID AS MAGID,"
MAGNITUDE.MAGNITUDE,MAGNITUDE.TYPE AS MAGTYPE "

from event.event join event.origin on event.id=origin.eventid
left join event.magnitude on origin.id=event.magnitude.origin_id "
WHERE EXISTS(select origin_id from event.magnitude where
magnitude.magnitude>=?

and my code is:

PreparedStatement stmt = psqlConnection.prepareStatement(sql,
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
org.postgresql.PGStatement pgstmt = (org.postgresql.PGStatement)stmt;
pgstmt.setPrepareThreshold(1);

boolean usingServerPrepare = pgstmt.isUseServerPrepare();
System.out.println("Execution: , Used server side: " + usingServerPrepare );

stmt.setDouble(1, new Double(7.5));
stmt.setFetchSize(200);
long old = System.currentTimeMillis();
ResultSet rs = stmt.executeQuery();
System.out.println("Execution time: "+(System.currentTimeMillis()-old)+"
ms.");

In fact the code is coming from the postgres manual to keep it very simple.
Now the interesting part is that after your email, I ran a
SELECT * FROM X WHERE X.ID>7

and it ran in 7 ms, but when did

SELECT * FROM X WHERE X.ID>?

It ran in 319 ms. While binding variables is slowing the query 10 times,
the time difference is not comparable to when I run my original query. 200
ms to 31000 ms. So my question is about execution plans. is the execution
plan is different when binding variables? I read as much as I can find from
the Postgres manual, but can't find something to hint about what I am doing
wrong.

By the way, I changed the value with for prepareThreshold but id didn't make
any difference. But the conclusion is binding variables based on the code I
run is always slower.

Thank you

On Wed, Feb 2, 2011 at 12:32 PM, Maciek Sakrejda <msakrejda(at)truviso(dot)com>wrote:

> My guess is the named (as opposed to anonymous) server-side prepared
> statements kick in and the planning is happening sans parameters (see
> the prepareThreshold connection parameter--see
>
> http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
> ), leading to a seq scan. Is it by chance only happening on the fifth
> invocation of that statement? Or are you changing prepareThreshold? If
> that's the case, you may want to bump prepareThreshold so that you
> never get a named statement here. If this is happening right off the
> bad and you're not mucking with the threshold, something more
> interesting is going on and we'd need more details on your Java code.
>
> ---
> Maciek Sakrejda | System Architect | Truviso
>
> 1065 E. Hillsdale Blvd., Suite 215
> Foster City, CA 94404
> www.truviso.com
>