From: | Pedro Zorzenon Neto <pedro2009(at)mandic(dot)com(dot)br> |
---|---|
To: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
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:44:55 |
Message-ID: | 4C31FDF7.7070204@mandic.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Zeeshan.Ghalib | 2010-07-05 15:47:48 | Re: Out of memory on update of a single column table containg just one row. |
Previous 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? |