pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
>  
>



view thread (7+ messages)  latest in thread

Message-ID: <40EF5D2C.5080506@coretech.co.nz>
Permalink:  ../40EF5D2C.5080506@coretech.co.nz/
Also on:    postgresql.org/message-id/40EF5D2C.5080506@coretech.co.nz

 · 

reply

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