X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id DA5279DC85E for ; Fri, 6 Jan 2006 05:21:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65134-10 for ; Fri, 6 Jan 2006 05:21:33 -0400 (AST) X-Greylist: delayed 18:04:38.378849 by SQLgrey- Received: from ar-sd.net (unknown [81.196.35.136]) by postgresql.org (Postfix) with ESMTP id 24B819DC803 for ; Fri, 6 Jan 2006 05:21:29 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 29A8B284B5 for ; Fri, 6 Jan 2006 11:21:31 +0200 (EET) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17835-04 for ; Fri, 6 Jan 2006 11:21:29 +0200 (EET) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id C0D2827488 for ; Fri, 6 Jan 2006 11:21:29 +0200 (EET) Message-ID: <007f01c612a2$94b1a5b0$0b00a8c0@forge> From: "Andy" To: References: <001d01c6120b$0d783600$0b00a8c0@forge> <20060105132006.19c7cd31.frank@wiles.org> Subject: Re: Improving Inner Join Performance Date: Fri, 6 Jan 2006 11:21:31 +0200 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/32 X-Sequence-Number: 16510 Yes I have indexes an all join fields. The tables have around 30 columns each and around 100k rows. The database is vacuumed every hour. Andy. ----- Original Message ----- From: "Frank Wiles" To: "Andy" Cc: Sent: Thursday, January 05, 2006 9:20 PM Subject: Re: [PERFORM] Improving Inner Join Performance > On Thu, 5 Jan 2006 17:16:47 +0200 > "Andy" wrote: > >> Hi to all, >> >> I have the following query: >> >> SELECT count(*) FROM orders o >> INNER JOIN report r ON r.id_order=o.id >> WHERE o.id_status>3 > >> How can I improve this query's performace?? The ideea is to count all >> the values that I have in the database for the following conditions. >> If the users puts in some other search fields on the where then the >> query runs faster but in this format sometimes it takes a lot lot of >> time(sometimes even 2,3 seconds). >> >> Can this be tuned somehow??? > > Do you have an index on report.id_order ? Try creating an index for > it if not and run a vacuum analyze on the table to see if it gets > rid of the sequence scan in the plan. > > --------------------------------- > Frank Wiles > http://www.wiles.org > --------------------------------- > > >