On Mon, Jul 05, 2010 at 12:44:55PM -0300, Pedro Zorzenon Neto wrote: > Em 05-07-2010 12:22, Sam Mason escreveu: > > SELECT DISTINCT ON (hardware_id) hardware_id, diag_value, ts > > FROM diagnose_logs > > WHERE ts <= '2009-12-25 23:59:59' > > ORDER BY hardware_id, ts DESC; > > It worked ok! your solution solves what I need. The process time went > from 60 to 20 seconds. nice! Always nice when less code is faster! > Can you help me to discover why the "Seq Scan" in explain analyse? I > tried to create some indexes to change seq scan to index scan, but > couldn't do it. It's because the only way PG knows how to do a DISTINCT ON is to sort the whole table and then pull out the appropriate values. Sorting the whole of a table is generally going to be faster than referring to an index for every row and hence PG won't use an index. I'm not sure if that's changed more recently, but for 7.4 I'm pretty sure that's the case anyway. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
