From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | 高增琦 <pgf00a(at)gmail(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: why do we need create tuplestore for each fetch? |
Date: | 2011-12-21 05:18:30 |
Message-ID: | 26443.1324444710@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
=?UTF-8?B?6auY5aKe55Cm?= <pgf00a(at)gmail(dot)com> writes:
> 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.
How are you trying to do the fetches, PQexec("fetch 3 in c") ?
That is an inherently inefficient way to do things, and trying to shave
a few cycles off the intermediate tuplestore isn't going to fix that.
The general overhead of parsing a new SQL command is probably going to
swamp the costs of a tuplestore, especially if it's too small to spill
to disk (and if it isn't, you really do need the tuplestore mechanism,
slow or not).
If you want to get a speed improvement there would probably be a lot
more bang for the buck in extending libpq to support protocol-level
portal access. It does already have PQdescribePortal, but for some
reason not anything for "fetch N rows from portal so-and-so". Not
sure whether it's worth providing explicit portal open/close commands
separate from PQexec'ing DECLARE CURSOR and CLOSE, but maybe at the
margins those steps would be worth improving too.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-12-21 05:29:54 | Re: CLOG contention |
Previous Message | Robert Haas | 2011-12-21 04:52:02 | CLOG contention |