Composite index planner issues Was: Re: Constraint exclusion oddity with composite index

Joshua D. Drake wrote:
> Tom Lane wrote:
>> "Joshua D. Drake" <jd@commandprompt.com> writes:
>>> Tom Lane wrote:
>>>> "Joshua D. Drake" <jd@commandprompt.com> writes:
>>>>> Assume the following:
>>>>> index on: (id, adate)
>>>>> constraint CHECK(adate > '01-01-2007' AND adate < '04-01-2007');
>>>>> The planner will not use the index listed above.
>>>> For what?
>>
>>> select adate from parent where adate = '01-25-2007'
>>
>> That's unsurprising.  Searching with only a lower-order index column
>> value seldom wins, 'cause you've got to scan the entire index.  The
>> constraint is irrelevant to this.
> 
> I guess where I got confused is:
> 
> http://www.postgresql.org/docs/8.1/static/indexes-multicolumn.html
> 
> And explicitly:
> 
> A multicolumn B-tree index can be used with query conditions that 
> involve any subset of the index's columns, but the index is most 
> efficient when there are constraints on the leading (leftmost) columns.

Considering the paragraph from the documentation above, should we change 
the documentation?

Joshua D. Drake


> 
> Sincerely,
> 
> Joshua D. Drake
> 
> 
>>
>>             regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
> 
>