Re: Performance problems with DISTINCT ON

"Sgarbossa Domenico" <domenico(dot)sgarbossa(at)eniac(dot)it> writes:
> I guess the right query is:

> select distinct on (articolo) articolo,data_ent,prezzo from listini_anagrafici order by articolo, data_ent desc

> but it seems that this query runs slowly... about 5/6 seconds.

> I've tried adding this index
> CREATE INDEX articolo_data_ent ON listini_anagrafici (articoli, data_ent)
> but it doesn't helps.

That index doesn't match the query ordering. You could do

select distinct on (articolo) articolo,data_ent,prezzo from listini_anagrafici order by articolo desc, data_ent desc

In more recent versions of Postgres you could make an index with one
column ascending and the other descending, but AFAIR 8.1 doesn't have
that.

regards, tom lane