Re: Order by clause in 'select distinct'?

On Wed, 13 Mar 2002, James Kelty wrote:

> When I create a view by a query like the follow query:
>
> SELECT DISTINCT ON (o.ordergenkey) o.ordergenkey, lip.payment FROM Ordr o,
> LineItemPayment lip
> WHERE o.ordergenkey = lip.orderfkey
> and o.ordergenkey = ?
>
> The view is created with an ORDER BY clause on the o.ordergenkey...
>
> I think it might be an enforce rule for createing a view with a DISTINCT ON
> phrase.
>
> I would like to, if possible, get rid of the ORDER BY clause as it slow the
> query to
> a crawl.
>
> Any ideas?

There's not actually an order by, but IIRC distinct on is done with
unique-sort-... plan so the values get sorted before passed to the routine
that does the actual distincting. I don't think there's a way to get rid
of the sorting step. Perhaps raising sort_mem will make the query run
better.