Port Bug Report: INSERT INTO SELECT with join creates over 2000 temp files

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Doug Mitchell
Your email address : doug(at)mitchcraft(dot)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

--------------------------------------------------------------------------