Re: Evaluation of secondary sort key.

On 09.04.2011 19:17, David Fetter wrote:
> On Sat, Apr 09, 2011 at 03:22:14PM +0200, Jesper Krogh wrote:
>> This seems like a place where there is room for improvement.
>>
>> 2011-04-09 15:18:08.016 testdb=# select id from test1 where id< 3
>> order by id;
>> id
>> ----
>> 1
>> 2
>> (2 rows)
>>
>> Time: 0.328 ms
>> 2011-04-09 15:18:11.936 testdb=# CREATE or Replace FUNCTION
>> testsort(id integer) returns integer as $$ BEGIN perform
>> pg_sleep(id); return id; END; $$ language plpgsql;
>> CREATE FUNCTION
>> Time: 12.349 ms
>> 2011-04-09 15:18:22.138 testdb=# select id from test1 where id< 3
>> order by id,testsort(id);
>> id
>> ----
>> 1
>> 2
>> (2 rows)
>>
>> Time: 3001.896 ms
>>
>> It seems strange that there is a need to evaluate testsort(id) at
>> all in this case.
>
> How would PostgreSQL know that sorting by id leaves no ambiguity for
> the next key to address?

Presumably there's a primary key constraint on id. This is one of those
cases where we could optimize, but then again, there's no reason to
write a query like that in the first place.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com