pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bill Chandler <billybobc1210@yahoo.com>
To: Mark Kirkwood <markir@coretech.co.nz>
Cc: pgsql-jdbc@postgresql.org
Cc: pgsql-perform@postgresql.org
Subject: Re: [PERFORM] Cursors performance
Date: Mon, 12 Jul 2004 11:07:29 -0700 (PDT)
Message-ID: <20040712180730.37055.qmail@web51407.mail.yahoo.com> (raw)
In-Reply-To: <40EF5D2C.5080506@coretech.co.nz>

Thanks,

Will try this test (I'm assuming you mean to say to
set fetch size of 1 and rerun on both JDBC and
psql).

BTW, here is another clue:  I only get the JDBC
performance degradation when I include the "real_name
like 'NEPOOL%REAL%'" clause.  I've tried re-ordering
too: i.e. putting this clause first in the statement,
last in the statement, etc.  Doesn't seem to make any
difference.

real_name is a varchar(64).  There is a unique index
on it.

thanks,

Bill

--- Mark Kirkwood <markir@coretech.co.nz> wrote:
> 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
> >  
> >
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>               
> http://www.postgresql.org/docs/faqs/FAQ.html
> 



		
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail



view thread (7+ messages)  latest in thread

Message-ID: <20040712180730.37055.qmail@web51407.mail.yahoo.com>
Permalink:  ../20040712180730.37055.qmail@web51407.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040712180730.37055.qmail@web51407.mail.yahoo.com

 · 

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: billybobc1210@yahoo.com, markir@coretech.co.nz, pgsql-jdbc@postgresql.org, pgsql-perform@postgresql.org
  Subject: Re: [PERFORM] Cursors performance
  In-Reply-To: <20040712180730.37055.qmail@web51407.mail.yahoo.com>

* 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