Constraint exclusion oddity with composite index

Hello,

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. It does work if we have
an index on just timehit in addition to the above. (of course)

Is this expected?

Joshua D. Drake

P.S. 8.1.9

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

"Joshua D. Drake" <jd(at)commandprompt(dot)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?

regards, tom lane

Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)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'

For example.

Joshua D. Drake

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> "Joshua D. Drake" <jd(at)commandprompt(dot)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.

regards, tom lane

Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> Tom Lane wrote:
>>> "Joshua D. Drake" <jd(at)commandprompt(dot)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.

Sincerely,

Joshua D. Drake

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

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

> >> Assume the following:
> >> index on: (id, adate)
> >> constraint CHECK(adate > '01-01-2007' AND adate < '04-01-2007');
> >

Um, the subject is CE, but the question is about an index ? Those are
separate issues.

> >> The planner will not use the index listed above.

> > For what?
>
> select adate from parent where adate = '01-25-2007'

A possibly cheaper plan would be a self join to produce all possible
id's and join the index for each (id, adate) pair.
Note, that you need not check visibility of the id's you produce (index
only access).
Is that what you were expecting ? This is not implemented.

Andreas

Joshua D. Drake wrote:
> Tom Lane wrote:
>> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>>> Tom Lane wrote:
>>>> "Joshua D. Drake" <jd(at)commandprompt(dot)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
>>
>
>

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> 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?

That statement seems perfectly accurate to me.

regards, tom lane

Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>>> 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?
>
> That statement seems perfectly accurate to me.

O.k. then perhaps I am being dense, but that statement says to me that
the planner should be able to use the right element of a composite index
but that it will not always do so.

Considering an index of a,b if I search for b I would expect that the
planner could use the index. Assuming of course that the planner would
use the same index if it was just b.

Further, I would expect a smaller chance of it using b if the index was
a,c,b but that it "might" still use it.

Is that not the case? Should I expect that even in the simplest of cases
that we will not use an index unless it is *the* leftmost element?

Sincerely,

Joshua D. Drake

>
> regards, tom lane
>

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> That statement seems perfectly accurate to me.

> Considering an index of a,b if I search for b I would expect that the
> planner could use the index.

It can. Whether it will think that's a good idea is another question
entirely, and one that seems a bit beyond the scope of the discussion
you're mentioning.

Try forcing the issue with enable_seqscan, and see what sort of
estimated and actual costs you get ...

regards, tom lane