Re: Prepared Statement Query Planning

Віталій Тимчишин wrote:
>
>
> 2009/8/29 Brett Henderson <brett(at)bretth(dot)com <mailto:brett(at)bretth(dot)com>>
>
> Oliver Jowett wrote:
>
> Brett Henderson wrote:
>
> It looks like you're using setFetchSize(). That forces use of
> a named statement regardless of prepareThreshold (we have to
> keep the statement and corresponding portal alive so we can do
> subsequent fetches, even if there are some other intervening
> queries, so we can't use the unnamed statement)
>
> Yes, I'm currently using a fetch size of 10000. I can't allow all
> results to be read at once because there can potentially be a huge
> number of results in the queries. I've just tested it out, and
> sure enough leaving the fetch size at 0 prevents the use of named
> statements.
>
>
> How about "create or replace temp view tmp_v as <query>" without
> setFetchSize and then "select * from tmp_v" with setFetchSize? Not
> sure still if DDL can use prepared query parameters.
I don't think I can do that. I couldn't use the syntax
"PREPARE STATEMENT mystatement (timestamp, timestamp) AS CREATE TEMP
VIEW tmp_nodes AS SELECT id, version FROM nodes WHERE timestamp > ? AND
timestamp <= ?"
> Another options would be either to use protocolLevel=2 (writing from
> memory, may misspell parameter name) - this will force client-side
> parameter embedding or to embed your timestamps into query text by
> yourself and not to use PreparedStatement at all. Fortunatelly this
> are not strings - so no quoting needed.
Will this force me to load all results in memory (ie. prevent me from
reading resultsets in batches)? I'm hesitant to force the older
protocol, I've read elsewhere that it should be avoided if possible
(something about exception handling??).

At this point it sounds like the lesser evil is to to specify some "set
local enable_seqscan = false" type statements. It seems to be working
well enough.

Brett