Skip site navigation (1) Skip section navigation (2)

Re: Prepared Statement Query Planning

From: Brett Henderson <brett(at)bretth(dot)com>
To: Віталій Тимчишин <tivv00(at)gmail(dot)com>
Cc: Oliver Jowett <oliver(at)opencloud(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Prepared Statement Query Planning
Date: 2009-08-30 06:24:43
Message-ID: 4A9A1B2B.2000600@bretth.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Віталій Тимчишин 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


In response to

pgsql-jdbc by date

Next:From: Damiano BolzoniDate: 2009-08-30 20:33:00
Subject: Search content within a bytea field
Previous:From: Brett HendersonDate: 2009-08-30 06:16:45
Subject: Re: Prepared Statement Query Planning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group