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 msUpdate 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.
