From: Mark Kirkwood <markir@coretech.co.nz>
To: Bill Chandler <billybobc1210@yahoo.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-jdbc@postgresql.org
Subject: Re: Cursors performance
Date: Sat, 10 Jul 2004 15:06:20 +1200
Message-ID: <40EF5D2C.5080506@coretech.co.nz> (raw)
In-Reply-To: <20040709202416.54013.qmail@web51401.mail.yahoo.com>
References: <20040709202416.54013.qmail@web51401.mail.yahoo.com>
Might be worth doing a little test:
i) modify your code to fetch 1 row at a time
ii) set log_duration=true in your postgresql.conf (as the other posters
have suggested)
Then compare with running the query in psql.
regards
Mark
Bill Chandler wrote:
>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 sent to the server as I process
>the result set from my query. Each time this command
>is sent it it takes about 5 minutes to return which is
>about the amount of time the whole query took to
>complete before the performance degredation. So in
>other words it looks as if the full select is being
>rerun on each fetch.
>
>Now the mystery is why is this happening all of the
>sudden? I have been running w/ fetch size set to 5000
>for the last couple of weeks and it did not appear to
>be doing this (i.e. re-running the entire select
>statement again). Is this what I should expect when
>using cursors? I would have thought that the server
>should "remember" where it left off in the query since
>the last fetch and continue from there.
>
>Could I have inadvertently changed a parameter
>somewhere that would cause this behavior?
>
>thanks,
>
>Bill
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam? Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: markir@coretech.co.nz, billybobc1210@yahoo.com, pgsql-jdbc@postgresql.org
Subject: Re: Cursors performance
In-Reply-To: <40EF5D2C.5080506@coretech.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox