|
I need to retrieve the most recent prices per
products from a price list table:
CREATE TABLE listini_anagrafici
( id character varying(36) NOT NULL, articolo character varying(18), listino character varying(5), data_ent date, data_fin date, prezzo double precision, ultimo boolean DEFAULT false, date_entered timestamp without time zone NOT NULL, date_modified timestamp without time zone NOT NULL, created_by character varying(36), modified_user_id character varying(36) NOT NULL, deleted boolean NOT NULL DEFAULT false, CONSTRAINT listini_anagrafici_id_key UNIQUE (id) ) 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.
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.
I've tried adding this index
CREATE INDEX articolo_data_ent ON
listini_anagrafici (articoli, data_ent)
but it doesn't helps.
As you can see from the explain command (below) the
query seems to ignore the index
'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' anyone knows how to make this query run
faster?
|
