To: "Jim C. Nasby" <jnasby@pervasive.com>
Cc: PFC <lists@peufeu.com>,  Greg Stark <gsstark@mit.edu>,
	Tom Lane <tgl@sss.pgh.pa.us>,  pgsql-performance@postgresql.org,
	pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
References: <445F7FF8.6070707@comcast.net>
	<1147110142.32369.7.camel@archimedes>
	<20060508175013.GY99570@pervasive.com>
	<28288.1147111196@sss.pgh.pa.us> <op.s89zl30wcigqcu@apollo13>
	<8764kfz1iq.fsf@stark.xeocode.com> <op.s9alfhjrcigqcu@apollo13>
	<20060510192401.GQ99570@pervasive.com>
In-Reply-To: <20060510192401.GQ99570@pervasive.com>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 11 May 2006 11:35:34 -0400
Message-ID: <87irocy3p5.fsf@stark.xeocode.com>
Lines: 37
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


"Jim C. Nasby" <jnasby@pervasive.com> writes:

> Perhaps it would be worth creating a class of temporary tables that used
> a tuplestore, although that would greatly limit what could be done with
> that temp table.

I can say that I've seen plenty of instances where the ability to create
temporary tables very quickly with no overhead over the original query would
be useful.

For instance, in one site I had to do exactly what I always advise others
against: use offset/limit to implement paging. So first I have to execute the
query with a count(*) aggregate to get the total, then execute the same query
a second time to fetch the actual page of interest. This would be (or could be
arranged to be) within the same transaction and doesn't require the ability to
execute any dml against the tuple store which I imagine would be the main
issues?

For bonus points what would be real neat would be if the database could notice
shared plan segments, keep around the materialized tuple store, and substitute
it instead of reexecuting that segment of the plan. Of course this requires
keeping track of transaction snapshot states and making sure it's still
correct.

> Something else worth considering is not using the normal catalog methods
> for storing information about temp tables, but hacking that together
> would probably be a rather large task.

It would be nice if using this feature didn't interact poorly with preplanning
all your queries and using the cached plans. Perhaps if you had some way to
create a single catalog entry that defined all the column names and types and
then simply pointed it at a new tuplestore each time without otherwise
altering the catalog entry?

-- 
greg