Re: [PERFORM] Cursors performance - Mailing list pgsql-jdbc

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