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