============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Doug Mitchell
Your email address : doug@mitchcraft.com
Category : runtime: back-end
Severity : non-critical
Summary: INSERT INTO SELECT with join creates over 2000 temp files
System Configuration
--------------------
Operating System : Linux 2.2.5 (RedHat 6.0)
PostgreSQL version : 6.5
Compiler used : gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Hardware:
---------
Linux sunfish 2.2.5-15 #1 Mon Apr 19 23:00:46 EDT 1999 i686 unknown
512 MB RAM
Versions of other tools:
------------------------
make-3.77-6
flex-2.5.4a-6
--------------------------------------------------------------------------
Problem Description:
--------------------
I ran the following query:
INSERT INTO table3 (fieldlist) SELECT fieldlist FROM table1,table2 WHERE table1.field = table2.field;
and found over 2000 pg_temp files, in my database directory,
all with the same pid, sequentially numbered. The join should
produce a few million records.
Note: tables 1 and 2 are TEMP tables and are indexed on the
fields being joined on.
The main problem here is that most Unix-style filesystems
have a major performance drop-off when there are hundreds
of files in a filesystem, if not thousands.
--------------------------------------------------------------------------
Test Case:
----------
Run the above query with really big tables.
--------------------------------------------------------------------------
Solution:
---------
I don't really see why temp tables are necessary for the join,
in the first place, there is not sorting.
*** PLEASE MAKE THE BOXES ON THE BUG REPORT FORM BIGGER ***
http://www.postgresql.org/doxlist.html
Thanks,
Doug
--------------------------------------------------------------------------