Re: avoid prepared statements on complex queries?

>I tried playing around with the prepareThreshold parameter, by setting it in the JDBC connection string. I tried setting it to zero, which should cause it to not use a prepared statement, right? Query is still >slow. I also tried setting it to 1 and 3, and in all cases it's slow. Is there any way to verify if postgres is using a planned query?

I think 0 should do it, yes (technically, it's using an unnamed
prepared statement rather than not using one at all, but this is the
behavior you're looking for). To verify, you should be able to change
log_min_messages (to debug2?) and see all plan/execute steps in the
server logs. Alternately, if you're not using SSL, you could just fire
up Wireshark and see what's on the wire (it has a built-in PostgreSQL
protocol plugin, so this is pretty straightforward). In either case,
the unnamed prepared statements should have no name (in Prepare, Bind,
and Execute messages); if memory serves, the named ones are something
like S_1, S_2, etc..

> "However even when plans are not cached, you will still run into the issue
> that an unnamed statement using out-of-line parameter values may generate a
> less efficient plan than an unnamed statement using inline parameter values,
> because there is no way to tell the planner at the protocol level "I am
> really only ever using this query once, please give me a specific plan for
> these values and don't worry about generating a plan that is correct for
> other values too"."
> Maybe that's what's happening?

Maybe, although I've never seen that happen and as I understand, the
driver always sends parameters along with the statement when using
unnamed statements, so I'm not sure what would trigger this. Perhaps
Oliver will chime in (he's still active on the list).

You may also want to try an EXPLAIN ANALYZE on your query in both
protocol versions and see what the plan differences are.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com