From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Composite keys
Date: Wed, 12 Oct 2011 06:26:33 -0400
Message-ID: <4E956B59.8030608@2ndQuadrant.com> (raw)
In-Reply-To: <EF3BA8897ED94C449F8D3C2722AE4210@CAPRICA>
References: <CCB4B01D184042ECBBE456D1ACB185A4@CAPRICA>
<CAGTBQpYMzj+xufEQBr7_aL3ZjZWaADU1oF40-ji8n8GcpZLj1A@mail.gmail.com>
<CALi4UpjWC15S8D3w-8u7TDVRdcq+PtHoaCOBSppxPiKw-BXJVw@mail.gmail.com>
<EF3BA8897ED94C449F8D3C2722AE4210@CAPRICA>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: Composite keys
In-Reply-To: <4E956B59.8030608@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox