pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave Cramer <pg@fastcrypt.com>
To: Bill Chandler <billybobc1210@yahoo.com>
Cc: pgsql-performance@postgresql.org, "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
Subject: Re: Cursors performance (was: Re: [PERFORM] Terrible
Date: Fri, 09 Jul 2004 16:39:01 -0400
Message-ID: <1089405541.3645.289.camel@localhost.localdomain> (raw)
In-Reply-To: <20040709202416.54013.qmail@web51401.mail.yahoo.com>
References: <20040709202416.54013.qmail@web51401.mail.yahoo.com>

Bill,

What happens if you do this in psql, also you can turn on duration
logging in the backend and log the queries.

dave
On Fri, 2004-07-09 at 16:24, 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 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
> 
> 
> 
> !DSPAM:40eefff6170301475214189!
> 
> 
-- 
Dave Cramer
519 939 0336
ICQ # 14675561




view thread (6+ messages)  latest in thread

Message-ID: <1089405541.3645.289.camel@localhost.localdomain>
Permalink:  ../1089405541.3645.289.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1089405541.3645.289.camel@localhost.localdomain

 · 

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: pg@fastcrypt.com, billybobc1210@yahoo.com, pgsql-jdbc@postgresql.org
  Subject: Re: Cursors performance (was: Re: [PERFORM] Terrible
  In-Reply-To: <1089405541.3645.289.camel@localhost.localdomain>

* 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