Re: how to remove a for-loop from programming language and put it into the query?

From: Thom Brown <thombrown(at)gmail(dot)com>
To: Pedro Zorzenon Neto <pedro2009(at)mandic(dot)com(dot)br>
Cc: 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:11:10
Message-ID: AANLkTilUo3GOZhW1clVHhQ2nFtJ5DZRuWfRSm2nOzfsD@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 5 July 2010 15:48, Pedro Zorzenon Neto <pedro2009(at)mandic(dot)com(dot)br> 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sam Mason 2010-07-05 15:22:05 Re: how to remove a for-loop from programming language and put it into the query?
Previous Message Pedro Zorzenon Neto 2010-07-05 14:48:37 how to remove a for-loop from programming language and put it into the query?