From: | Tim Landscheidt <tim(at)tim-landscheidt(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how to remove a for-loop from programming language and put it into the query? |
Date: | 2010-07-05 15:30:23 |
Message-ID: | m3fwzyosj4.fsf@passepartout.tim-landscheidt.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Pedro Zorzenon Neto <pedro2009(at)mandic(dot)com(dot)br> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2010-07-05 15:32:55 | Re: how to remove a for-loop from programming language and put it into the query? |
Previous Message | Pedro Zorzenon Neto | 2010-07-05 15:26:43 | Re: how to remove a for-loop from programming language and put it into the query? |