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

[Pedro Zorzenon Neto <pedro2009@xxxxxxxxxxxxx>]

Hello!

Can someone help me to develop a query?

Things are more complex than this example, but with this simple example
I can explain what I need and get an answer.

Table: "diagnose_logs"
Fields:
  - id serial
  - hardware_id integer
  - diag_value integer
  - ts timestamp

So I collect many diagnose information from many hardwares.

So, I need to get a report of all diagnostics of all hardware on
december 25th.

(external programming language)
for ($i = 1; $i < 500; $i++) {
  // return me the "most recent" diag_value from a hardware_id $i
  // at the desired timestamp
  runquery("select diag_value from diagnose_logs where ts <= '2009-12-25
23:59:59' and hardware_id = $i order by ts desc limit 1");
}

Currently I have an index on diagnose_logs(ts,hardware_id)
I have 3 milion registers of 500 different hardware_id.

The time to run 500 times this query is long... about 1 minute. When I
need a montly day-by-day report of 500 hardwares, it takes about half an
hour.

can I turn this for-loop into a single query to run in postgres?

Thanks,
Pedro


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general