>>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> 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.
Note that the 150 disk writes were for the CREATE and the DROP. Does
that mean that we'd actually shave 84 of 150 writes?
Also, if you're looking to account for all the writes, it's worth
noting that my test declared a one-column primary key (on an integer
column) in the CREATE TEMPORARY TABLE statement.
In suggesting this enhancement, my hope is that each session could
check for a referenced table as a temporary in RAM before going to the
system tables, in a manner vaguely similar to how space reserved by
the temp_buffers GUC is used for temp table data. I, of course, am
suggesting this from a position of blissful ignorance of the actual
complexity of making such a change.
-Kevin