X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id EDE0E925118 for ; Wed, 21 May 2003 07:30:34 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 05342-04 for ; Wed, 21 May 2003 07:30:26 -0400 (EDT) Received: from news.hub.org (unknown [64.117.225.234]) by developer.postgresql.org (Postfix) with ESMTP id 62DC29250FF for ; Wed, 21 May 2003 07:30:26 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id 66E842534AC; Wed, 21 May 2003 07:30:22 -0400 (EDT) Message-ID: From: David W Noon Subject: Re: Performance on temp table inserts X-Newsgroups: comp.databases.postgresql.sql References: <4d5op-4h3.ln1@my-pc.ntlworld.com> Lines: 39 User-Agent: KNode/0.7.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7Bit Date: Wed, 21 May 2003 12:03:13 +0100 X-Complaints-To: abuse@ntlworld.com Organization: ntl Cablemodem News Service To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/423 X-Sequence-Number: 13588 On Tuesday 20 May 2003 17:21 in , Jeff Boes (jboes@nexcerpt.com) wrote: > explain insert into temp_link_checksums values ('a'); > NOTICE: QUERY PLAN: > > Result (cost=0.00..0.01 rows=1 width=0) > > EXPLAIN > > This is identical on the two systems. As I would expect for an insert > statement ... Ok. > But the table doesn't exist before I create and insert it. (Well, > actually it does: after the first CREATE, the subsequent uses of the > table are preceded by TRUNCATE TABLE, so it exists but is empty. I dunno > what a VACUUM ANALYZE will do for an insert, though.) The reason I suggested a VACUUM was that my thought processes on this problem lead me to the same conclusion I would have come to 25 years ago: DASD fragmentation. My reasoning is that the PostgreSQL catalogue on the test box is possibly more fragmented than on the production box because developers tend to do more CREATEs and DROPs of new tables than do production users. Moreover, the DASD farm backing PostgreSQL could be similarly fragmented. If a VACUUM does not have a beneficial effect then it would seem that the underlying physical filesystem could be severely fragmented. -- Regards, Dave [RLU#314465] ====================================================== dwnoon@spamtrap.ntlworld.com (David W Noon) Remove spam trap to reply via e-mail. ======================================================