Thread: Port Bug Report: INSERT INTO SELECT with join creates over 2000 temp files
Port Bug Report: INSERT INTO SELECT with join creates over 2000 temp files
From
Unprivileged user
Date:
============================================================================ 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 --------------------------------------------------------------------------