From: Barry Lind <blind@xythos.com>
To: Bill Chandler <billybobc1210@yahoo.com>
Cc: 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 14:05:12 -0700
Message-ID: <40F2FD08.7020106@xythos.com> (raw)
In-Reply-To: <20040712180730.37055.qmail@web51407.mail.yahoo.com>
References: <20040712180730.37055.qmail@web51407.mail.yahoo.com>
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
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: blind@xythos.com, billybobc1210@yahoo.com, markir@coretech.co.nz, pgsql-jdbc@postgresql.org, pgsql-perform@postgresql.org
Subject: Re: [PERFORM] Cursors performance
In-Reply-To: <40F2FD08.7020106@xythos.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