Sgarbossa Domenico wrote: > I need to retrieve the most recent prices per products from a price list > table:
> 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. > the table contains more or less 500K records, PostgreSQL version is 8.1.11 > and the server has 4gb of RAM entirely dedicate to the db. > 'Unique (cost=73893.89..76551.25 rows=88312 width=24) (actual > time=4022.578..5076.206 rows=193820 loops=1)' > ' -> Sort (cost=73893.89..75222.57 rows=531472 width=24) (actual > time=4022.574..4505.538 rows=531472 loops=1)' > ' Sort Key: articolo, data_ent' > ' -> Seq Scan on listini_anagrafici (cost=0.00..16603.72 rows=531472 > width=24) (actual time=0.009..671.797 rows=531472 loops=1)' > 'Total runtime: 5217.452 ms' You've got 531472 rows in the table and the query is going to output 193820 of them. Scanning the whole table is almost certainly the way to go. If the table doesn't change much, you could try running a CLUSTER on the index you've created. That will lock the table while it re-orders the physical layout of the rows based on your index though, so it's no good if the table is updated much. Failing that, you could try issuing "set work_mem = ..." before the query with increasing sizes for work_mem. That might make the sort faster too. -- Richard Huxton Archonet Ltd -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
