Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Big IN() clauses etc : feature proposal

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


In response to

Responses

pgsql-performance by date

Next:From: Craig A. JamesDate: 2006-05-11 15:53:34
Subject: Re: Postgres gets stuck
Previous:From: Qingqing ZhouDate: 2006-05-11 15:26:12
Subject: Re: Postgres gets stuck

pgsql-hackers by date

Next:From: Martijn van OosterhoutDate: 2006-05-11 16:41:51
Subject: Re: Bug in signal handler
Previous:From: Mark CampbellDate: 2006-05-11 15:20:52
Subject: Re: Compiling on 8.1.3 on Openserver 5.05

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group