The index can produce the sorted output. Add a dummy WHERE clause like articoli > <min_value> and data_ent > <min_value>.
--Imad On Mon, Sep 28, 2009 at 10:18 PM, Sgarbossa Domenico <domenico.sgarbo...@eniac.it> wrote: > > 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=73897.58..76554.94 rows=77765 width=24)' > ' -> Sort (cost=73897.58..75226.26 rows=531472 width=24)' > ' Sort Key: articolo, data_ent' > ' -> Seq Scan on listini_anagrafici (cost=0.00..16603.72 > rows=531472 width=24)' > > anyone knows how to make this query run faster? > > > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
