>>> Alvaro Herrera <[EMAIL PROTECTED]> 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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
