Обсуждение: Order by clause in 'select distinct'?

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?

-James

James Kelty
Sr. Unix Systems Administrator
The Ashland Agency
541.488.0801
jamesk@ashlandagency.com

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.