Re: avoid prepared statements on complex queries?

Hi Kris,

Sorry, if I wasn't clear.  I'm doing everything in JDBC.

If I use a prepared statement, and set my main parameter to be an array of 150 integers, the query takes for ever (1200 seconds).  If I instead skip using a prepared statement by doing the following, the query takes 30 seconds:
Statement stmt = conn.createStatement();
stmt.setFetchSize(1000);
ResultSet rs = stmt.executeQuery(QUERY_STRING);

where QUERY_STRING is the entire query with the 150 integers.

In both cases, I'm calling a SQL function:
create or replace function foo(int,int[],int[],int, boolean, boolean, double precision)
.
.
where
        dataset_id = any($2)
.
.
$$ language 'sql' immutable;

the second parameter is the one where the array will verify anywhere from 1 to 500 integers.

I'm going to try Maciek's suggestions (server logging, and wire shark),  to see if I can verify that when I set ?prepareThreshold=0 in the JDBC string, it is in fact forcing it to be an unnamed prepared statement.

thanks,
Anish

________________________________
From: Kris Jurka <books(at)ejurka(dot)com>
To: Anish Kejariwal <anishkej(at)yahoo(dot)com>
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Sent: Wednesday, November 16, 2011 1:20 PM
Subject: Re: [JDBC] avoid prepared statements on complex queries?

On Tue, 15 Nov 2011, Anish Kejariwal wrote:

> I'm running into an issue where a complex query is performing significantly
> slower if I use a prepared statement.
>
> Some notes about the query:
> -I'm calling a postgres function, via: select * from foo(?,?,?,?,?,?,?)
> -I'm getting back 4 million results
> -the underlying query is quite complex joining several tables, and the core
> table is partitioned over a couple hundred tables.
>
> Here's the java code:
>
> conn.setAutoCommit(false);
> pstmt = conn.prepareStatement("select * from foo(?,?,?,?,?,?,?)");
> pstmt.setFetchSize(1000);
>
> //and, then I do the usual thing to set parameters in the prepared statement
> stmt.setInt(1, SOME_NUMBER);
> pstmt.setArray(2, genepoolConn.createArrayOf("integer", ARRAY_OF_INTEGERS));
> //and so on
>
> So, obviously when using a prepared statement, postgres is coming up with
> the wrong execution plan when the the parameter list is unexpectedly large.

You haven't really shown that.  Unless you issue the same query, manually
interpolating in the query parameters and get a much faster time, all
you've shown is that things get slower with a larger number of values.

What language is your function written in?  Unless it's a sql function
which could be inlined, I wouldn't have expected any difference between
prepared vs inline query execution.

Kris Jurka