Re: Prepared Statement Query Planning

Oliver Jowett wrote:
> Brett Henderson wrote:
>
>> The full JDBC trace with loglevel=2 is below.  Is it possible to tell
>> whether server side prepared statements are being used from this?
>> Note that this has been obtained from a windows laptop running
>> PostgreSQL 8.3.5, but the real problem is occurring on an Ubuntu
>> Linux server running PostgreSQL 8.3.7.  The same JDBC driver is being
>> used across the board.
>
>> 17:48:46.443 (1)  FE=> Parse(stmt=S_2,query="SELECT e.id, e.version,
>> e.timestamp, e.visible, u.data_public, u.id AS user_id,
>> u.display_name, e.changeset_id, e.latitude, e.longitude FROM nodes e
>> LEFT OUTER JOIN changesets c ON e.changeset_id = c.id LEFT OUTER JOIN
>> users u ON c.user_id = u.id WHERE e.timestamp > $1 AND e.timestamp <=
>> $2 ORDER BY e.id, e.version",oids={0,0})
>> 17:48:46.444 (1)  FE=> Describe(statement=S_2)
>> 17:48:46.444 (1)  FE=> Bind(stmt=S_2,portal=C_3,$1=<1970-01-01
>> 10:00:00.000000 +10:00:00>,$2=<2009-08-28 17:48:45.932000 +10:00:00>)
>> 17:48:46.445 (1)  FE=> Execute(portal=C_3,limit=10000)
>> 17:48:46.445 (1)  FE=> Sync
>
> This is using a named statement (S_2 is the name)
>
> 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.

It sounds like I'm stuck with forcing the query planner via set
statements.  I'm currently using "set enable_seqscan=false;set
enable_hashjoin=false;set enable_mergejoin=false" and getting a good
query plan.

I'm not familiar with PostgreSQL internals, but I assume a portal is
basically a cursor?  So is there no way of creating a cursor and
fetching results in batches using an unnamed statement?

Brett