Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 66594B5DBD9 for ; Wed, 12 Oct 2011 07:26:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 76520-07 for ; Wed, 12 Oct 2011 10:26:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.171]) by mail.postgresql.org (Postfix) with ESMTP id BADA6B5DBDB for ; Wed, 12 Oct 2011 07:26:36 -0300 (ADT) Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mrbap3) with ESMTP (Nemesis) id 0MWBdh-1RblKc03MP-00XGJ0; Wed, 12 Oct 2011 12:26:35 +0200 Message-ID: <4E956B59.8030608@2ndQuadrant.com> Date: Wed, 12 Oct 2011 06:26:33 -0400 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20111004 Icedove/3.0.11 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Composite keys References:

In-Reply-To: Content-Type: multipart/alternative; boundary="------------070100000901040605000408" X-Provags-ID: V02:K0:TuvK0WUHg6PimDyG/3EREGrn4k16detM3Rif+Bjem6V h2EJh6SK/NMYhukxVnkf3njpLZxrS17LOv+5EQYIjPNNptoDei anVik+Qim5qs7OOtltqHUD/YKZNaYc+sfFHlqCqyGIA0A38l1v mMENP1Wec+1OFBkKBvyFWeOK8H1faZOEKdSChzjHeQpegbtawh wDQlotegi5FWX6kDeigQBiaUwD+eGCq7nFZKEDNLgSNni+Rrbh TuvJYdN6bXCnqqYA1csLp40wUogq1w4JTzDu2ZTE4cOEgqo+SJ V8auG3A54V0YDZv+IO3QmCRvwHkJ0cJCbYennixVEDlQtGQHw6 MKHsDJDktJZ45juIfn30= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201110/128 X-Sequence-Number: 45350 This is a multi-part message in MIME format. --------------070100000901040605000408 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 10/12/2011 12:39 AM, Carlo Stonebanks wrote: > > So with PG I will stick to the general SQL rule that IF I use compound > keys then we have the most selective columns to the left... correct? > There was a subtle point Dave made you should pay close attention to though. If there are multiple indexes that start with the same column, PostgreSQL is biased toward picking the smallest of them. The amount of extra I/O needed to navigate a wider index is such that the second column has to be very selective, too, before it will be used instead of a narrower single column one. There are plenty of times that the reason behind "why isn't it using my index?" is "the index is too fat to navigate efficiently", because the actual number of blocks involved is factored into the cost computations. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us --------------070100000901040605000408 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On 10/12/2011 12:39 AM, Carlo Stonebanks wrote:

So with PG I will stick to the general SQL rule that IF I use compound keys then we have the most selective columns to the left… correct?

There was a subtle point Dave made you should pay close attention to though. If there are multiple indexes that start with the same column, PostgreSQL is biased toward picking the smallest of them. The amount of extra I/O needed to navigate a wider index is such that the second column has to be very selective, too, before it will be used instead of a narrower single column one. There are plenty of times that the reason behind "why isn't it using my index?" is "the index is too fat to navigate efficiently", because the actual number of blocks involved is factored into the cost computations.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us

--------------070100000901040605000408--