pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Cursors performance
7+ messages / 6 participants
[nested] [flat]

* Re: Cursors performance
@ 2004-07-09 21:55  Oliver Jowett <oliver@opencloud.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Oliver Jowett @ 2004-07-09 21:55 UTC (permalink / raw)
  To: Bill Chandler <billybobc1210@yahoo.com>; +Cc: pgsql-performance; pgsql-jdbc@postgresql.org

Bill Chandler wrote:

> 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.

I'd check heap size, GC activity (-verbose:gc), CPU use, swapping 
activity on the *client* side. It may be that your dataset size or 
physical memory or something similar has changed sufficiently that GC 
resulting from the data in each 5k row batch is killing you.

Can you try a trivial app that runs the same query (with same fetchsize, 
autocommit, etc) via JDBC and does nothing but steps forward through the 
resultset, and see how fast it runs? Perhaps the problem is in your 
processing logic.

-O



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Cursors performance
@ 2004-07-10 03:06  Mark Kirkwood <markir@coretech.co.nz>
  1 sibling, 1 reply; 7+ messages in thread

From: Mark Kirkwood @ 2004-07-10 03:06 UTC (permalink / raw)
  To: Bill Chandler <billybobc1210@yahoo.com>; +Cc: pgsql-performance; pgsql-jdbc@postgresql.org

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
>  
>



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [PERFORM] Cursors performance
@ 2004-07-12 18:07  Bill Chandler <billybobc1210@yahoo.com>
  parent: Mark Kirkwood <markir@coretech.co.nz>
  0 siblings, 1 reply; 7+ messages in thread

From: Bill Chandler @ 2004-07-12 18:07 UTC (permalink / raw)
  To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: pgsql-jdbc@postgresql.org; pgsql-perform@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [PERFORM] Cursors performance
@ 2004-07-12 21:05  Barry Lind <blind@xythos.com>
  parent: Bill Chandler <billybobc1210@yahoo.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Barry Lind @ 2004-07-12 21:05 UTC (permalink / raw)
  To: Bill Chandler <billybobc1210@yahoo.com>; +Cc: Mark Kirkwood <markir@coretech.co.nz>; pgsql-jdbc@postgresql.org; pgsql-perform@postgresql.org

Bill,

I suspect that this is an artifact of using server side prepared 
statements.  When testing this via psql you will be forming sql like:

select ...
from ...
where ...
and real_name like 'NEPOOL%REAL%'
...

but the JDBC driver with server side prepared statements is doing:

select ...
from ...
where ...
and real_name like ?
...

So when the statement is prepared, since it doesn't know what values you 
are going to use in the bind variable, it will generally take a more 
concervative execution plan than if it knows what the bind variable is.

So I suspect the performance difference is just in the different 
execution plans for the two different forms of the sql statement.

thanks,
--Barry


Bill Chandler wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [PERFORM] Cursors performance
@ 2004-07-12 21:11  Kris Jurka <books@ejurka.com>
  parent: Barry Lind <blind@xythos.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Kris Jurka @ 2004-07-12 21:11 UTC (permalink / raw)
  To: Barry Lind <blind@xythos.com>; +Cc: Bill Chandler <billybobc1210@yahoo.com>; Mark Kirkwood <markir@coretech.co.nz>; pgsql-jdbc@postgresql.org; pgsql-perform@postgresql.org



On Mon, 12 Jul 2004, Barry Lind wrote:

> Bill,
> 
> I suspect that this is an artifact of using server side prepared 
> statements.  When testing this via psql you will be forming sql like:

I don't think so.  The 7.4 driver can use either cursors or server
prepared statements, not both.  He's definitely using cursors, so I server 
prepared statements don't come into the mix here.

Kris Jurka




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [PERFORM] Cursors performance
@ 2004-07-13 16:13  Bill Chandler <billybobc1210@yahoo.com>
  parent: Kris Jurka <books@ejurka.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Bill Chandler @ 2004-07-13 16:13 UTC (permalink / raw)
  To: Kris Jurka <books@ejurka.com>; +Cc: pgsql-jdbc@postgresql.org; pgsql-perform@postgresql.org

All,

Looks like I may have beaten this one to death.  May
have to chalk it up to a limitation for now due to
deadlines and revisit it later.  

One final clue before I go: if I change my wildcard to
'NEPOOL%' from 'NEPOOL%REAL%' my query completes much
faster.  Of course this makes sense since it's much
easier to search a string for a prefix than it is to
do a complex regular expression match.  I just didn't
expect it to be orders of magnitude difference.

The table containing the string being searched is only
7500 rows but I am joining it with a table with
2.5 million rows.  So maybe there's something I can do
to do the wildcard search on the smaller table first
then do the join.  

Ok, thanks again to all who responded.  Really
appreciate the tips on logging statements and
duration, etc.

regards,

Bill
--- Kris Jurka <books@ejurka.com> wrote:
> 
> 
> On Mon, 12 Jul 2004, Barry Lind wrote:
> 
> > Bill,
> > 
> > I suspect that this is an artifact of using server
> side prepared 
> > statements.  When testing this via psql you will
> be forming sql like:
> 
> I don't think so.  The 7.4 driver can use either
> cursors or server
> prepared statements, not both.  He's definitely
> using cursors, so I server 
> prepared statements don't come into the mix here.
> 
> Kris Jurka
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> 



		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [PERFORM] Cursors performance
@ 2004-07-13 17:08  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bill Chandler <billybobc1210@yahoo.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2004-07-13 17:08 UTC (permalink / raw)
  To: Bill Chandler <billybobc1210@yahoo.com>; +Cc: Kris Jurka <books@ejurka.com>; pgsql-jdbc@postgresql.org; pgsql-perform@postgresql.org

Bill Chandler <billybobc1210@yahoo.com> writes:
> One final clue before I go: if I change my wildcard to
> 'NEPOOL%' from 'NEPOOL%REAL%' my query completes much
> faster.

Could we see the exact queries and EXPLAIN ANALYZE output for both
cases?  I'm wondering if the plan changes.  I think that the planner
will believe that the latter pattern is significantly more selective
(how much more selective depends on exactly which PG version you're
using); if this results in a bad row-count estimate then a bad plan
could get picked.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2004-07-13 17:08 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-09 21:55 ` Oliver Jowett <oliver@opencloud.com>
2004-07-10 03:06 ` Mark Kirkwood <markir@coretech.co.nz>
2004-07-12 18:07   ` Bill Chandler <billybobc1210@yahoo.com>
2004-07-12 21:05     ` Barry Lind <blind@xythos.com>
2004-07-12 21:11       ` Kris Jurka <books@ejurka.com>
2004-07-13 16:13         ` Bill Chandler <billybobc1210@yahoo.com>
2004-07-13 17:08           ` Tom Lane <tgl@sss.pgh.pa.us>

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