Вот такая интересная ситуация :

2016-07-22 09:58:21 UTC 172.25.41.65 23535 LOG: duration: 32841.048 ms statement: SELECT itemid,round(1778* MOD(CAST(clock AS BIGINT)+223331,259200)/(259200),0) AS i,COUNT(*) AS count,AVG(value) AS avg,MIN(value) AS min,MAX(value) AS max,MAX(clock) AS clock FROM history_uint WHERE itemid='400400000245245' AND clock>='1468922269' AND clock<='1469181469' GROUP BY itemid,round(1778* MOD(CAST(clock AS BIGINT)+223331,259200)/(259200),0)

explain analyze SELECT itemid,round(1778* MOD(CAST(clock AS BIGINT)+223331,259200)/(259200),0) AS i,COUNT(*) AS count,AVG(value) AS avg,MIN(value) AS min,MAX(value) AS max,MAX(clock) AS clock FROM history_uint WHERE itemid='400400000245245' AND clock>='1468922269' AND clock<='1469181469' GROUP BY itemid,round(1778* MOD(CAST(clock AS BIGINT)+223331,259200)/(259200),0);

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=54294.26..54714.95 rows=14023 width=16) (actual time=92.952..94.024 rows=1711 loops=1)
-> Result (cost=0.00..54048.86 rows=14023 width=16) (actual time=0.584..81.852 rows=4145 loops=1)
-> Append (cost=0.00..53803.45 rows=14023 width=16) (actual time=0.574..77.018 rows=4145 loops=1)
-> Seq Scan on history_uint (cost=0.00..1.03 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=1)
Filter: ((clock >= 1468922269) AND (clock <= 1469181469) AND (itemid = 400400000245245::bigint))
Rows Removed by Filter: 2
-> Bitmap Heap Scan on history_uint_p2016_07_19 (cost=110.33..10268.21 rows=2648 width=16) (actual time=0.568..14.733 rows=794 loops=1)
Recheck Cond: ((itemid = 400400000245245::bigint) AND (clock >= 1468922269) AND (clock <= 1469181469))
-> Bitmap Index Scan on history_uint_p2016_07_19_1 (cost=0.00..109.67 rows=2648 width=0) (actual time=0.441..0.441 rows=794 loops=1)
Index Cond: ((itemid = 400400000245245::bigint) AND (clock >= 1468922269) AND (clock <= 1469181469))
-> Bitmap Heap Scan on history_uint_p2016_07_20 (cost=196.03..17826.77 rows=4664 width=16) (actual time=0.809..23.544 rows=1370 loops=1)
Recheck Cond: ((itemid = 400400000245245::bigint) AND (clock >= 1468922269) AND (clock <= 1469181469))
-> Bitmap Index Scan on history_uint_p2016_07_20_1 (cost=0.00..194.87 rows=4664 width=0) (actual time=0.607..0.607 rows=1370 loops=1)
Index Cond: ((itemid = 400400000245245::bigint) AND (clock >= 1468922269) AND (clock <= 1469181469))
-> Bitmap Heap Scan on history_uint_p2016_07_21 (cost=196.97..18148.59 rows=4737 width=16) (actual time=0.764..24.910 rows=1416 loops=1)
Recheck Cond: ((itemid = 400400000245245::bigint) AND (clock >= 1468922269) AND (clock <= 1469181469))
-> Bitmap Index Scan on history_uint_p2016_07_21_1 (cost=0.00..195.78 rows=4737 width=0) (actual time=0.556..0.556 rows=1416 loops=1)
Index Cond: ((itemid = 400400000245245::bigint) AND (clock >= 1468922269) AND (clock <= 1469181469))
-> Bitmap Heap Scan on history_uint_p2016_07_22 (cost=81.72..7558.85 rows=1973 width=16) (actual time=0.434..12.953 rows=565 loops=1)
Recheck Cond: ((itemid = 400400000245245::bigint) AND (clock >= 1468922269) AND (clock <= 1469181469))
-> Bitmap Index Scan on history_uint_p2016_07_22_1 (cost=0.00..81.23 rows=1973 width=0) (actual time=0.343..0.343 rows=565 loops=1)
Index Cond: ((itemid = 400400000245245::bigint) AND (clock >= 1468922269) AND (clock <= 1469181469))
Total runtime: 94.510 ms


Все таки 32 секунды и 94 миллисекунды весьма существенная разница.
Кроме как периодические существенные нагрузки на систему IO , мне пока никаких версий не приходит.

Спасибо.

Maxim Boguk,

Спасибо, значиь в верном направлении думаю.
По графику утилизация Disk I/O utilization = 100%

Все больше убеждаюсь, что узкое место системы - не база.