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 A251A9DC816 for ; Fri, 6 Jan 2006 11:26:42 -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 33346-06 for ; Fri, 6 Jan 2006 11:26:46 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.192]) by postgresql.org (Postfix) with ESMTP id 36B549DC863 for ; Fri, 6 Jan 2006 11:26:38 -0400 (AST) Received: by wproxy.gmail.com with SMTP id 57so2849132wri for ; Fri, 06 Jan 2006 07:26:44 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Gjdv1v+vE+9xqgOsanWJzgu7frvcqxwAMkiV4ZpGPcQEKu8JEtbgMPPqZBex2z4TY90VGf3VgUFm03yy3KsOtLigPXBhGHfgG9OW+7ugwm5y75MIHjce4xEAfLOgWC8GglnRv/vlzosjwwHieT8fv7Si5AZLyxY01fXc5aaIEn4= Received: by 10.54.114.7 with SMTP id m7mr5319889wrc; Fri, 06 Jan 2006 07:26:43 -0800 (PST) Received: by 10.54.92.14 with HTTP; Fri, 6 Jan 2006 07:26:43 -0800 (PST) Message-ID: Date: Fri, 6 Jan 2006 10:26:43 -0500 From: Jaime Casanova To: Andy Subject: Re: Improving Inner Join Performance Cc: Pandurangan R S , pgsql-performance@postgresql.org In-Reply-To: <00c001c612aa$f39ed1d0$0b00a8c0@forge> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <001d01c6120b$0d783600$0b00a8c0@forge> <5e744e3d0601060156y3f68ad0dn152c946254ee9fd3@mail.gmail.com> <00c001c612aa$f39ed1d0$0b00a8c0@forge> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.082 required=5 tests=[AWL=0.082] X-Spam-Score: 0.082 X-Spam-Level: X-Archive-Number: 200601/39 X-Sequence-Number: 16517 On 1/6/06, Andy wrote: > At the moment: o.id_status>3 can have values only 4 and 6. The 6 is aroun= d > 90% from the whole table. This is why seq scan is made. > given this if you make id_status > 3 you will never use an index because you will be scanning 4 and 6 the only values in this field as you say, and even if there were any other value 6 is 90% of whole table, so an index for this will not be used... > Now, depending on the user input the query can have more where fields. Fo= r > example: > SELECT count(*) FROM orders o > INNER JOIN report r ON r.id_order=3Do.id > WHERE o.id_status > 3 AND r.id_zufriden=3D7 > here the planner can be more selective, and of course the query is faster... if you will be loading data load it all then make tests... but because your actual data the planner will always choose to scan the entire orders table for o.id_status > 3... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)