Re: Order by clause in 'select distinct'?
Re: Order by clause in 'select distinct'?
От:
Stephan Szabo <sszabo@megazone23.bigpanda.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.
Order by clause in 'select distinct'?
От:
"James Kelty" <jamesk@ashlandagency.com>
Дата:
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
