Re: how to remove a for-loop from programming language and put it into the query?

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/