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 B3A9C329F05 for ; Wed, 20 Oct 2004 00:21:32 +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 61243-07 for ; Tue, 19 Oct 2004 23:21:20 +0000 (GMT) Received: from mail.pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id B0ADB329D86 for ; Wed, 20 Oct 2004 00:21:20 +0100 (BST) Received: (qmail 22105 invoked by uid 0); 20 Oct 2004 09:21:16 +1000 Received: from cpe-203-45-44-212.vic.bigpond.net.au (HELO wizzard.pws.com.au) (russell@pws.com.au@203.45.44.212) by mail.pws.com.au with SMTP; 20 Oct 2004 09:21:16 +1000 From: Russell Smith To: Gavin Sherry Subject: Re: Select with qualified join condition / Batch inserts Date: Wed, 20 Oct 2004 09:18:57 +1000 User-Agent: KMail/1.7 Cc: Bernd , pgsql-performance@postgresql.org References: <200410151225.26083.bernd_pg@genedata.com> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200410200918.57761.mr-russ@pws.com.au> 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/298 X-Sequence-Number: 8774 On Fri, 15 Oct 2004 08:47 pm, Gavin Sherry wrote: > On Fri, 15 Oct 2004, Bernd wrote: > > > Hi, [snip] > > Table-def: > > Table "public.scr_well_compound" > > Column | Type | Modifiers > > ------------+------------------------+----------- > > mat_id | numeric(10,0) | not null > > barcode | character varying(240) | not null > > well_index | numeric(5,0) | not null > > id_level | numeric(3,0) | not null > > compound | character varying(240) | not null > > Indexes: > > "scr_wcm_pk" PRIMARY KEY, btree (id_level, mat_id, barcode, well_index) > numeric is not optimized by postgresql like it is by Oracle. You will get much better performance by changing the numeric types to int, big int, or small int. That should get the query time down to somewhere near what Oracle is giving you. Regards Russell Smith. [snip]