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