From: | Pedro Zorzenon Neto <pedro2009(at)mandic(dot)com(dot)br> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | how to remove a for-loop from programming language and put it into the query? |
Date: | 2010-07-05 14:48:37 |
Message-ID: | 4C31F0C5.7060402@mandic.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2010-07-05 15:11:10 | Re: how to remove a for-loop from programming language and put it into the query? |
Previous Message | Zeeshan.Ghalib | 2010-07-05 14:44:59 | Re: Out of memory on update of a single column table containg just one row. |