On 5 July 2010 15:48, Pedro Zorzenon Neto <pedro2009@xxxxxxxxxxxxx> wrote: > 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 I'm probably misunderstanding the problem, but can't you just do: SELECT diag_value FROM diagnose_logs WHERE ts <= '2009-12-25 23:59:59' AND hardware_id BETWEEN 1 AND 500 ORDER BY ts DESC LIMIT 1 Regards Thom -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general