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

Em 05-07-2010 12:22, Sam Mason escreveu:
>
> You want to be using DISTINCT ON or some sort of WINDOW function.
> DISTINCT ON works with older version of PG, but isn't as standards'
> conforming.  The following should do the trick with DISTINCT ON:
>
>   SELECT DISTINCT ON (hardware_id) hardware_id, diag_value, ts
>   FROM diagnose_logs
>   WHERE ts <= '2009-12-25 23:59:59'
>   ORDER BY hardware_id, ts DESC;
>
> You can obviously put in the normal clauses to limit the hardware_ids to
> be things you consider important in the normal ways.

Hi Sam!

It worked ok! your solution solves what I need. The process time went
from 60 to 20 seconds. nice!

Can you help me to discover why the "Seq Scan" in explain analyse? I
tried to create some indexes to change seq scan to index scan, but
couldn't do it.

Now the real table and field names...

explain analyse select distinct on (callbox_id) callbox_id, ts, imei,
temperatura from diag_resultados where ts <= '2010-06-15 00:00:00' order
by callbox_id, ts desc;
                                                               QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=408118.90..417725.43 rows=406 width=18) (actual
time=19608.347..22626.744 rows=458 loops=1)
   ->  Sort  (cost=408118.90..412922.17 rows=1921306 width=18) (actual
time=19608.345..21503.135 rows=1905941 loops=1)
         Sort Key: callbox_id, ts
         ->  Seq Scan on diag_resultados  (cost=0.00..58795.50
rows=1921306 width=18) (actual time=0.024..4886.113 rows=1905941 loops=1)
               Filter: (ts <= '2010-06-15 00:00:00-03'::timestamp with
time zone)
 Total runtime: 22762.754 ms

Pg is old in this machine. 7.4.17