Pedro Zorzenon Neto <pedro2009@xxxxxxxxxxxxx> wrote: > [...] > 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? Another month, another case for "DISTINCT ON": | SELECT DISTINCT ON (hardware_id) | hardware_id, diag_value | FROM diagnose_logs | WHERE ts <= '2009-12-25 23:59:59' | ORDER BY hardware_id, ts DESC; BTW, I'd prefer "WHERE ts < '2009-12-26'" as otherwise you don't catch a timestamp '2009-12-25 23:59:59.5' (not to speak of leap seconds). Tim -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general