From: Greg Stark <gsstark@mit.edu>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: PFC <lists@peufeu.com>
Cc: Greg Stark <gsstark@mit.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
Date: 11 May 2006 11:35:34 -0400
Message-ID: <87irocy3p5.fsf@stark.xeocode.com> (raw)
In-Reply-To: <20060510192401.GQ99570@pervasive.com>
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>
"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
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: gsstark@mit.edu, jnasby@pervasive.com, lists@peufeu.com, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
In-Reply-To: <87irocy3p5.fsf@stark.xeocode.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