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