From: | Brett Henderson <brett(at)bretth(dot)com> |
---|---|
To: | Oliver Jowett <oliver(at)opencloud(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Prepared Statement Query Planning |
Date: | 2009-08-29 12:40:36 |
Message-ID: | 4A9921C4.30907@bretth.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-08-29 14:52:45 | Re: Prepared Statement Query Planning |
Previous Message | Mario Splivalo | 2009-08-29 11:18:32 | Re: Inserting 'large' amounts of data |