Re: Help with slow table update

On Mon, Apr 13, 2015 at 5:01 PM, Pawel Veselov <pawel(dot)veselov(at)gmail(dot)com>
wrote:

>
> r_agrio_hourly - "good", r_agrio_total - "bad".
>
> Update on r_agrio_hourly (cost=0.42..970.32 rows=250 width=329) (actual
> time=2.248..2.248 rows=0 loops=1)
> -> Index Scan using u_r_agrio_hourly on r_agrio_hourly
> (cost=0.42..970.32 rows=250 width=329) (actual time=0.968..1.207 rows=1
> loops=1)
> Index Cond: ((tagid = 1002::numeric) AND (unitid = 1002::numeric)
> AND ((rowdate)::text = '2015-04-09T23'::text) AND (device_type =
> 3::numeric) AND (placement = 2::numeric))
> Total runtime: 2.281 ms
> Update on r_agrio_total (cost=0.42..45052.56 rows=12068 width=321)
> (actual time=106.766..106.766 rows=0 loops=1)
> -> Index Scan using u_r_agrio_total on r_agrio_total
> (cost=0.42..45052.56 rows=12068 width=321) (actual time=0.936..32.626
> rows=1 loops=1)
> Index Cond: ((tagid = 1002::numeric) AND (unitid = 1002::numeric)
> AND (device_type = 3::numeric) AND (placement = 2::numeric))
> Total runtime: 106.793 ms
>

What it is you expect to see here?

​What are the results (count and times) for:

SELECT count(*) FROM r_agrio_total WHERE tagid = 1002 and unitid = 1002;
SELECT count(*) FROM r_agrio_hourly WHERE tagid = 1002 and unitid = 1002;

​More queries along this line might be needed. The underlying question is
how many index rows need to be skipped over on "total" to get the final
result - or rather are the columns in the index in descending order of
cardinality?

Any chance you can perform a "REINDEX" - maybe there is some bloat
present? There are queries to help discern if that may be the case, I do
not know then off the top of my head, but just doing it might be acceptable
and is definitely quicker if so.

​I'm still not really following your presentation but maybe my thoughts
will spark something.​

​David J.
​