Re: Evaluation of secondary sort key.

On Sat, Apr 09, 2011 at 07:24:15PM +0300, Heikki Linnakangas wrote:
> 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.

Given the horrors query generators perpetrate, it might be worth
dropping provably redundant ORDER BYs on the floor at planning time.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate