Re: Composite keys

Greg Smith <greg@xxxxxxxxxxxxxxx> · Wed, 12 Oct 2011 06:26:33 -0400

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@xxxxxxxxxxxxxxx   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us