Thanks for you reply. I found query without cursor is faster then query with server-side cursor and several fetches. But I have a large result set to retrieve from database. I have to choose server-side cursor to avoid out-of-memory problem.
When I try to debug the cursor and fetch, I found this unexpected behavior. I think maybe the tuplestore slows the cursor. (maybe I should do some profile later) I want to change the code, but I am afraid there are important reasons for the tuplestore. Therefore, I post it to this list for help: why create tuplestore for each fetch? p.s. a large fetch may turn tuplestore to use buffer file, and slow the performance very much. On Mon, Dec 19, 2011 at 9:06 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Dec 15, 2011 at 8:30 AM, 高增琦 <pgf...@gmail.com> wrote: > > I found this several days ago when I try to debug a "fetch" of cursor. > > And I have sent a mail to this list, but no one reply... > > Maybe this is a very simple problem, please help me, thanks a lot... > > > > Here is the example: > > create table t (a int); > > insert into t values (1),(3),(5),(7),(9); > > insert into t select a+1 from t; > > begin; > > declare c cursor for select * from t order by a; > > fetch 3 in c; > > fetch 3 in c; > > fetch 3 in c; > > > > In 'PortalRun', a fetch stmt will be treated with PORTAL_UTIL_SELECT, > > and then a tuplestore will be created in 'FillPortalStore' in the > > fetch stmt's portal. > > > > In 'FillPortalStore', all result will be store at that tuplestore, > > Then, go back to 'PortalRun'; next, 'PortalRunSelect' will send this > > results to client... > > > > My problem is: why do we need create that tuplestore as an > > middle storeage? why do not we just send these result to clent > > at the first time? > > Good question. I wouldn't expect it to matter very much for a > three-row fetch, but maybe it does for larger ones? What is your > motivation for investigating this? > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- GaoZengqi pgf...@gmail.com zengqi...@gmail.com
