Thanks to all who have responded. I now think my
problem is not related to deleting/recreating indexes.
Somehow it is related to JDBC cursors. It appears
that what is happening is that since I'm using
a fetch size of 5000, the command:
FETCH FORWARD 5000 FROM JDBC_CURS_1
is being repeatedly se
Thanks for this tip. Turns out there is a difference.
I am using cursors (i.e. calling setFetchSize(5000) on
my Statement) in JDBC. So the SQL statement is
preceded by:
DECLARE JDBC_CURS_1 CURSOR FOR ...
which is then followed by the SQL statemnt.
This is followed by the separate statement:
That is interesting - both psql and JDBC merely submit statements for
the backend to process, so generally you would expect no difference in
execution plan or performance.
It might be worth setting "log_statement=true" in postgresql.conf and
checking that you are executing *exactly* the same st
Thanks for the advice.
On further review it appears I am only getting this
performance degradation when I run the command via
a JDBC app. If I do the exact same query from
psql, the performance is fine. I've tried both the
JDBC2 and JDBC3 jars. Same results.
It definitely seems to correspond
Bill Chandler wrote:
Hi,
Using PostgreSQL 7.4.2 on Solaris. I'm trying to
improve performance on some queries to my databases so
I wanted to try out various index structures.
Since I'm going to be running my performance tests
repeatedly, I created some SQL scripts to delete and
recreate vario
Hi,
Using PostgreSQL 7.4.2 on Solaris. I'm trying to
improve performance on some queries to my databases so
I wanted to try out various index structures.
Since I'm going to be running my performance tests
repeatedly, I created some SQL scripts to delete and
recreate various index configurati