X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A3F7F32C05E for ; Fri, 15 Oct 2004 18:08:21 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94863-09 for ; Fri, 15 Oct 2004 17:08:15 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 21D5F32C03F for ; Fri, 15 Oct 2004 18:08:13 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9FH81KD010424; Fri, 15 Oct 2004 13:08:01 -0400 (EDT) To: Bernd Cc: pgsql-performance@postgresql.org Subject: Re: Select with qualified join condition / Batch inserts In-reply-to: <200410151225.26083.bernd_pg@genedata.com> References: <200410151225.26083.bernd_pg@genedata.com> Comments: In-reply-to Bernd message dated "Fri, 15 Oct 2004 12:25:26 +0200" Date: Fri, 15 Oct 2004 13:08:01 -0400 Message-ID: <10423.1097860081@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/223 X-Sequence-Number: 8699 Bernd writes: > 1/ The following query takes about 5 sec. with postrgres whereas on Oracle it > executes in about 30 ms (although both tables only contain 200 k records in > the postgres version). What does EXPLAIN ANALYZE have to say about it? Have you ANALYZEd the tables involved in the query? You would in any case be very well advised to change the "numeric" columns to integer, bigint, or smallint when appropriate. There is a substantial performance advantage to using the simple integral datatypes instead of the general numeric type. regards, tom lane