Re: Composite keys

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us