Обсуждение: Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)
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
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
On Fri, 9 Jul 2004, 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 > If the top level node of your execution plan is a sort step, it should take essentially no time to retrieve additional rows after the first fetch. The sort step is materializes the results so that future fetches simply need to spit this data back to the client. I would agree with Dave's suggestion to use log_duration and compare the values for the first and subsequent fetches. Kris Jurka
Using psql it peforms exactly as I'd expect. The rows get printed out to stdout, I hold down the space bar to keep everything scrolling and as every 5000 rows go by I see a new fetch statement logged in the server log. The results from this statement seem to come back instaneously and the output starts scrolling again immediately. Whole query takes a few minutes to complete. I seems like it has something to do w/ my JDBC app but I can't think for the life of me what I might have changed. Anyway, there's only the setFetchSize(5000) and the setAutoCommit(false) that are relevant to cursors, right? And those have been in there for weeks. Bill --- Dave Cramer <pg@fastcrypt.com> wrote: > 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 > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Ok, given that there are 5000 rows, the jdbc driver will actually fetch all 5000 when you do the fetch, so is it the speed of the connection, or the actual fetch that is taking the time, again, check the server logs for duration. Dave On Fri, 2004-07-09 at 17:03, Bill Chandler wrote: > Using psql it peforms exactly as I'd expect. The > rows get printed out to stdout, I hold down the space > bar to keep everything scrolling and as every 5000 > rows go by I see a new fetch statement logged in the > server log. The results from this statement seem to > come back instaneously and the output starts scrolling > again immediately. Whole query takes a few minutes > to complete. > > I seems like it has something to do w/ my JDBC app > but I can't think for the life of me what I might have > changed. Anyway, there's only the setFetchSize(5000) > and the setAutoCommit(false) that are relevant to > cursors, right? And those have been in there for > weeks. > > Bill > > --- Dave Cramer <pg@fastcrypt.com> wrote: > > 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 > > > > > > > > > > > > > > > > > > > > -- > > Dave Cramer > > 519 939 0336 > > ICQ # 14675561 > > > > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > > > !DSPAM:40ef083f256273772718645! > > -- Dave Cramer 519 939 0336 ICQ # 14675561
I should have said the results below are from running the query via JDBC. I'll get some results from psql and post those as well. --- Bill Chandler <billybobc1210@yahoo.com> wrote: > Here are the result from "log_duration = true" > > DECLARE/1st FETCH: 325014.881 ms > 2nd FETCH: 324055.281 ms > > --- Dave Cramer <pg@fastcrypt.com> wrote: > > Ok, given that there are 5000 rows, the jdbc > driver > > will actually fetch > > all 5000 when you do the fetch, so is it the speed > > of the connection, or > > the actual fetch that is taking the time, again, > > check the server logs > > for duration. > > > > Dave > > On Fri, 2004-07-09 at 17:03, Bill Chandler wrote: > > > Using psql it peforms exactly as I'd expect. > The > > > rows get printed out to stdout, I hold down the > > space > > > bar to keep everything scrolling and as every > 5000 > > > rows go by I see a new fetch statement logged in > > the > > > server log. The results from this statement > seem > > to > > > come back instaneously and the output starts > > scrolling > > > again immediately. Whole query takes a few > > minutes > > > to complete. > > > > > > I seems like it has something to do w/ my JDBC > app > > > but I can't think for the life of me what I > might > > have > > > changed. Anyway, there's only the > > setFetchSize(5000) > > > and the setAutoCommit(false) that are relevant > to > > > cursors, right? And those have been in there > for > > > weeks. > > > > > > Bill > > > > > > --- Dave Cramer <pg@fastcrypt.com> wrote: > > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > Dave Cramer > > > > 519 939 0336 > > > > ICQ # 14675561 > > > > > > > > > > > > > > > > > > __________________________________________________ > > > Do You Yahoo!? > > > Tired of spam? Yahoo! Mail has the best spam > > protection around > > > http://mail.yahoo.com > > > > > > > > > > > > !DSPAM:40ef083f256273772718645! > > > > > > > > -- > > Dave Cramer > > 519 939 0336 > > ICQ # 14675561 > > > > > > > > > __________________________________ > Do you Yahoo!? > New and Improved Yahoo! Mail - Send 10MB messages! > http://promotions.yahoo.com/new_mail > __________________________________ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail
Here are the result from "log_duration = true" DECLARE/1st FETCH: 325014.881 ms 2nd FETCH: 324055.281 ms --- Dave Cramer <pg@fastcrypt.com> wrote: > Ok, given that there are 5000 rows, the jdbc driver > will actually fetch > all 5000 when you do the fetch, so is it the speed > of the connection, or > the actual fetch that is taking the time, again, > check the server logs > for duration. > > Dave > On Fri, 2004-07-09 at 17:03, Bill Chandler wrote: > > Using psql it peforms exactly as I'd expect. The > > rows get printed out to stdout, I hold down the > space > > bar to keep everything scrolling and as every 5000 > > rows go by I see a new fetch statement logged in > the > > server log. The results from this statement seem > to > > come back instaneously and the output starts > scrolling > > again immediately. Whole query takes a few > minutes > > to complete. > > > > I seems like it has something to do w/ my JDBC app > > but I can't think for the life of me what I might > have > > changed. Anyway, there's only the > setFetchSize(5000) > > and the setAutoCommit(false) that are relevant to > > cursors, right? And those have been in there for > > weeks. > > > > Bill > > > > --- Dave Cramer <pg@fastcrypt.com> wrote: > > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > Dave Cramer > > > 519 939 0336 > > > ICQ # 14675561 > > > > > > > > > > > > __________________________________________________ > > Do You Yahoo!? > > Tired of spam? Yahoo! Mail has the best spam > protection around > > http://mail.yahoo.com > > > > > > > > !DSPAM:40ef083f256273772718645! > > > > > -- > Dave Cramer > 519 939 0336 > ICQ # 14675561 > > __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail
Using psql I get the following results: DECLARE: 37.556 ms 1st FETCH: 553210.220 ms 2nd FETCH: 420.555 ms 3rd FETCH: 433.639 ms 4th FETCH: 421.009 ms 5th FETCH: 436.240 ms So using psql the 1st fetch is much slower but all subsequent ones are almost instantaneous. Whereas using JDBC each fetch takes about 5.5 minutes. Another curious thing (at least to me): if I turn off cursors altogether in my JDBC app and just get the entire result set (about 101000 rows) the whole thing returns in 68 seconds. This seems counter-intuitive to me. Why would it take 68 seconds to return everything but 305 minutes to just return the 1st 5000? thanks, Bill --- Bill Chandler <billybobc1210@yahoo.com> wrote: > I should have said the results below are from > running the query via JDBC. I'll get some > results from psql and post those as well. > > --- Bill Chandler <billybobc1210@yahoo.com> wrote: > > Here are the result from "log_duration = true" > > > > DECLARE/1st FETCH: 325014.881 ms > > 2nd FETCH: 324055.281 ms > > __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail
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
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 > >
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
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
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
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
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