Re: RAM-only temporary tables

Kevin Grittner wrote:
> An idea for a possible enhancement to PostgreSQL: allow creation of a
> temporary table without generating any disk I/O. (Creating and
> dropping a three-column temporary table within a database transaction
> currently generates about 150 disk writes).

Most of these are catalog updates. A trace of WAL logs including only
heap inserts says that to create a temp table with 3 columns (2 int, 1
text) and no indexes there are this many inserts:

3 1247 (pg_type)
20 1249 (pg_attribute)
3 1259 (pg_class)
7 2608 (pg_depend)
1 2610 (pg_index)

Note the excess of pg_attribute entries! There are 3 in the table, 3 in
the toast table, and then there are 14 extra attrs which are for system
columns (7 for the main table, 7 for the toast table). Just getting rid
of pg_attribute entries for those would probably prove to be an
importante gain. (Don't forget the index updates for each of those heap
inserts; for pg_type it's 2 btree inserts for each index insert.) If
you do this, you've shaved 42 of those 150 writes.

Perhaps another gain is getting rid of array types for temp tables; a
new feature of 8.3. Who wants those anyway? That also removes one or
two of the pg_depend entries. Maybe these extra array types are the
additional 8.3 overhead.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.