Re: Order by optimisations?

On 7/14/05, Michael Paesold wrote:
> Christopher Kings-Lynne wrote:
>>
>> usatest=# explain select * from users_myfoods_map where date='2004-11-21'
>> order by date;
>> QUERY PLAN
>> ---------------------------------------------------------------------------
>> Sort (cost=17.17..17.48 rows=123 width=22)
>> Sort Key: date
>> -> Seq Scan on users_myfoods_map (cost=0.00..12.90 rows=123 width=22)
>> Filter: (date = '2004-11-21'::date)
>> (4 rows)
>>
>> The sort cost is non-zero. Or am I not looking at the right thing...
>
> You are looking at the right thing, AFAIK. Well, it seems the planner cannot
> reason that if a field should have only one value, sorting on that field is
> not needed.

For the planner to deduct that, it should first deduct that the field
should only have one value. Is that a deduction the planner can even
make for this query if we consider for instance implicit timestamp to
date casting?

> I remember there are examples where the planner will know that the input to
>a sort is already sorted and will skip the sort.

The planner knows the output of an indexscan is sorted. With a proper
index on the "date" field (I hope that is not really the name) and
favourable statistics the planner should switch to an indexscan and
the order node should disappear.

Jochem