shared_buffers = 2GB
work_mem = 64MB
maintenance_work_mem = 2GB

Сервер SRV-Legion 1U 2xXeon® 5500/5600\12xDDR3 ECC REG (192Gb/1333)\VGA Matrox 8Mb\Onboard Intel SW RAID 0, 1, 10\2*PCI-E 8X\4xUSB\2xGigabit Ethernet (Intel 82576)\до 4 HDD c горячей заменой\Рельсы\PSU 2x650W c резервированием\IPMI+IPKVM 1 шт

Intel Xeon E5620 (2.4GHz // QPI 5.86 GT/s // DDR3 1066MHz, cache 12MB, Quad, TB, HT, 80W) 2 шт

Жесткий диск 2TB SATA II 7200RPM Enterprise 4 шт

Контроллер LSI 9240 4i (2MB, 4xSAS/SATA channels, RAID0, 1, 10, 5 no BBU support) 1 шт

Модуль памяти 4GB ECC 1333 DDR3 Reg 8 шт

2014-09-20 15:35:26 SAMT ???????: checkpoint complete: wrote 777 buffers (0.3%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=77.527 s, sync=0.333 s, total=77.917 s; sync files=91, longest=0.048 s, average=0.003 s
2014-09-20 15:39:08 SAMT ???????: checkpoint starting: time
2014-09-20 15:41:38 SAMT ???????: checkpoint complete: wrote 1566 buffers (0.6%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=149.173 s, sync=0.159 s, total=149.351 s; sync files=95, longest=0.030 s, average=0.001 s
2014-09-20 15:44:08 SAMT ???????: checkpoint starting: time
2014-09-20 15:45:26 SAMT ???????: checkpoint complete: wrote 779 buffers (0.3%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=77.884 s, sync=0.200 s, total=78.104 s; sync files=94, longest=0.053 s, average=0.002 s
2014-09-20 15:49:08 SAMT ???????: checkpoint starting: time
2014-09-20 15:50:08 SAMT ???????: checkpoint complete: wrote 598 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=59.639 s, sync=0.094 s, total=59.801 s; sync files=96, longest=0.014 s, average=0.000 s
2014-09-20 15:54:08 SAMT ???????: checkpoint starting: time
2014-09-20 15:55:07 SAMT ???????: checkpoint complete: wrote 592 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=59.127 s, sync=0.174 s, total=59.307 s; sync files=94, longest=0.042 s, average=0.001 s
2014-09-20 15:59:08 SAMT ???????: checkpoint starting: time
2014-09-20 16:00:31 SAMT ???????: checkpoint complete: wrote 822 buffers (0.3%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=82.120 s, sync=0.189 s, total=82.394 s; sync files=90, longest=0.052 s, average=0.002 s
2014-09-20 16:04:08 SAMT ???????: checkpoint starting: time
2014-09-20 16:05:00 SAMT ???????: checkpoint complete: wrote 517 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=51.811 s, sync=0.197 s, total=52.032 s; sync files=84, longest=0.021 s, average=0.002 s
2014-09-20 16:09:08 SAMT ???????: checkpoint starting: time
2014-09-20 16:10:02 SAMT ???????: checkpoint complete: wrote 540 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=53.950 s, sync=0.127 s, total=54.082 s; sync files=84, longest=0.027 s, average=0.001 s
2014-09-20 16:14:08 SAMT ???????: checkpoint starting: time
2014-09-20 16:15:02 SAMT ???????: checkpoint complete: wrote 534 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=53.283 s, sync=0.213 s, total=53.526 s; sync files=82, longest=0.041 s, average=0.002 s
2014-09-20 16:19:08 SAMT ???????: checkpoint starting: time
2014-09-20 16:20:03 SAMT ???????: checkpoint complete: wrote 554 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=55.448 s, sync=0.231 s, total=55.748 s; sync files=86, longest=0.037 s, average=0.002 s
2014-09-20 16:24:09 SAMT ???????: checkpoint starting: time
2014-09-20 16:25:04 SAMT ???????: checkpoint complete: wrote 551 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=55.106 s, sync=0.422 s, total=55.591 s; sync files=85, longest=0.049 s, average=0.004 s
2014-09-20 16:29:09 SAMT ???????: checkpoint starting: time
2014-09-20 16:30:05 SAMT ???????: checkpoint complete: wrote 556 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=55.469 s, sync=0.186 s, total=55.686 s; sync files=80, longest=0.031 s, average=0.002 s
2014-09-20 16:34:09 SAMT ???????: checkpoint starting: time
2014-09-20 16:35:03 SAMT ???????: checkpoint complete: wrote 538 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=53.849 s, sync=0.123 s, total=53.998 s; sync files=85, longest=0.023 s, average=0.001 s
2014-09-20 16:39:09 SAMT ???????: checkpoint starting: time
2014-09-20 16:40:05 SAMT ???????: checkpoint complete: wrote 557 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=55.585 s, sync=0.256 s, total=55.898 s; sync files=88, longest=0.030 s, average=0.002 s
2014-09-20 16:44:09 SAMT ???????: checkpoint starting: time
2014-09-20 16:45:03 SAMT ???????: checkpoint complete: wrote 541 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=53.931 s, sync=0.398 s, total=54.369 s; sync files=93, longest=0.142 s, average=0.004 s
2014-09-20 16:49:10 SAMT ???????: checkpoint starting: time
2014-09-20 16:51:41 SAMT ???????: checkpoint complete: wrote 2625 buffers (1.0%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=149.892 s, sync=0.979 s, total=150.965 s; sync files=105, longest=0.241 s, average=0.009 s
2014-09-20 16:54:10 SAMT ???????: checkpoint starting: time
2014-09-20 16:55:08 SAMT ???????: checkpoint complete: wrote 577 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=57.724 s, sync=0.262 s, total=58.041 s; sync files=97, longest=0.058 s, average=0.002 s
2014-09-20 16:59:10 SAMT ???????: checkpoint starting: time
2014-09-20 17:00:42 SAMT ???????: checkpoint complete: wrote 914 buffers (0.3%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=91.479 s, sync=0.289 s, total=91.774 s; sync files=99, longest=0.042 s, average=0.002 s
2014-09-20 17:04:10 SAMT ???????: checkpoint starting: time
2014-09-20 17:05:16 SAMT ???????: checkpoint complete: wrote 661 buffers (0.3%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=66.234 s, sync=0.283 s, total=66.598 s; sync files=94, longest=0.053 s, average=0.003 s
2014-09-20 17:09:10 SAMT ???????: checkpoint starting: time
2014-09-20 17:09:48 SAMT ???????: checkpoint complete: wrote 367 buffers (0.1%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=36.735 s, sync=0.464 s, total=37.217 s; sync files=84, longest=0.107 s, average=0.005 s
2014-09-20 17:14:10 SAMT ???????: checkpoint starting: time
2014-09-20 17:14:56 SAMT ???????: checkpoint complete: wrote 457 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=46.197 s, sync=0.412 s, total=46.642 s; sync files=82, longest=0.071 s, average=0.005 s
2014-09-20 17:19:10 SAMT ???????: checkpoint starting: time
2014-09-20 17:20:12 SAMT ???????: checkpoint complete: wrote 613 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=61.261 s, sync=0.441 s, total=61.718 s; sync files=90, longest=0.074 s, average=0.004 s
2014-09-20 17:24:10 SAMT ???????: checkpoint starting: time
2014-09-20 17:24:55 SAMT ???????: checkpoint complete: wrote 442 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=44.154 s, sync=0.341 s, total=44.572 s; sync files=84, longest=0.091 s, average=0.004 s
2014-09-20 17:29:10 SAMT ???????: checkpoint starting: time
2014-09-20 17:30:41 SAMT ???????: checkpoint complete: wrote 897 buffers (0.3%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=90.633 s, sync=0.380 s, total=91.051 s; sync files=96, longest=0.053 s, average=0.003 s
2014-09-20 17:34:10 SAMT ???????: checkpoint starting: time
2014-09-20 17:35:06 SAMT ???????: checkpoint complete: wrote 560 buffers (0.2%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=56.186 s, sync=0.260 s, total=56.514 s; sync files=92, longest=0.032 s, average=0.002 s

00:00:01 DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
15:35:01 dev8-0 11.18 1.28 319.20 28.66 0.10 8.81 2.65 2.96
15:45:01 dev8-0 7.16 1.25 216.45 30.40 0.09 11.90 3.61 2.58
15:55:01 dev8-0 7.75 7.59 194.04 26.01 0.09 11.29 3.47 2.69
16:05:01 dev8-0 7.24 0.01 187.58 25.90 0.07 9.69 3.27 2.37
16:15:01 dev8-0 6.21 0.43 160.10 25.86 0.07 11.75 3.76 2.33
16:25:01 dev8-0 7.89 4.70 551.06 70.47 0.44 55.30 3.93 3.10
16:35:01 dev8-0 6.95 0.43 174.61 25.17 0.07 10.68 3.70 2.57
16:45:01 dev8-0 8.54 1.49 201.04 23.71 0.06 7.55 2.67 2.28
16:55:01 dev8-0 38.40 903.96 314.22 31.72 0.59 15.29 1.06 4.06
17:05:01 dev8-0 25.93 300.74 190.52 18.95 0.12 4.56 1.58 4.10
17:15:01 dev8-0 1502.55 23934.45 192.22 16.06 1.30 0.87 0.63 95.18
17:25:01 dev8-0 1522.94 24259.18 188.55 16.05 1.37 0.90 0.62 95.12
17:35:01 dev8-0 1461.36 23261.93 251.86 16.09 1.57 1.08 0.65 95.49

00:00:01 CPU %user %nice %system %iowait %steal %idle
15:35:01 all 3.27 0.00 0.36 0.14 0.00 96.23
15:45:01 all 4.03 0.00 0.73 0.14 0.00 95.10
15:55:01 all 3.94 0.00 0.70 0.16 0.00 95.20
16:05:01 all 4.28 0.00 0.90 0.13 0.00 94.69
16:15:01 all 4.24 0.00 0.88 0.13 0.00 94.75
16:25:01 all 4.37 0.00 0.87 0.19 0.00 94.57
16:35:01 all 4.10 0.00 0.81 0.13 0.00 94.95
16:45:01 all 4.13 0.00 0.79 0.11 0.00 94.96
16:55:01 all 4.16 0.00 0.79 0.20 0.00 94.84
17:05:01 all 4.37 0.00 0.85 0.31 0.00 94.47
17:15:01 all 5.26 0.00 1.67 11.84 0.00 81.24
17:25:01 all 4.95 0.00 1.55 11.86 0.00 81.64
17:35:01 all 4.72 0.00 1.38 12.04 0.00 81.86

> iostat -x -m 1 -c 1

avg-cpu: %user %nice %system %iowait %steal %idle
4.14 0.00 0.65 3.93 0.00 91.28

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.52 5.27 172.33 22.49 3.74 3.54 76.56 2.72 13.96 2.58 101.14 1.06 20.69

avg-cpu: %user %nice %system %iowait %steal %idle
4.14 0.00 0.65 3.93 0.00 91.28

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.52 5.27 172.49 22.49 3.74 3.54 76.50 2.72 13.95 2.58 101.14 1.06 20.70

avg-cpu: %user %nice %system %iowait %steal %idle
4.14 0.00 0.65 3.93 0.00 91.28

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.52 5.27 172.58 22.49 3.74 3.54 76.47 2.72 13.94 2.58 101.14 1.06 20.71

avg-cpu: %user %nice %system %iowait %steal %idle
4.14 0.00 0.65 3.93 0.00 91.28

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.52 5.27 172.83 22.49 3.74 3.54 76.38 2.72 13.93 2.58 101.14 1.06 20.72

> iostat -x -m 1 -c 1
avg-cpu: %user %nice %system %iowait %steal %idle
4.14 0.00 0.65 3.93 0.00 91.28

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.52 5.27 172.86 22.48 3.74 3.54 76.37 2.72 13.92 2.58 101.14 1.06 20.72

avg-cpu: %user %nice %system %iowait %steal %idle
4.14 0.00 0.65 3.93 0.00 91.28

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.52 5.27 172.92 22.48 3.74 3.54 76.35 2.72 13.92 2.58 101.14 1.06 20.73

avg-cpu: %user %nice %system %iowait %steal %idle
4.14 0.00 0.65 3.93 0.00 91.28

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.52 5.27 172.93 22.48 3.74 3.54 76.34 2.72 13.92 2.58 101.14 1.06 20.73

avg-cpu: %user %nice %system %iowait %steal %idle
4.14 0.00 0.65 3.93 0.00 91.28

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.52 5.27 173.00 22.48 3.74 3.54 76.32 2.72 13.91 2.58 101.14 1.06 20.73

avg-cpu: %user %nice %system %iowait %steal %idle
4.14 0.00 0.65 3.94 0.00 91.27

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.52 5.27 173.83 22.48 3.75 3.54 76.03 2.72 13.85 2.57 101.13 1.06 20.78

CREATE OR REPLACE FUNCTION GetBottomInfo(did_ integer) RETURNS SETOF varchar LANGUAGE plpgsql AS $$
DECLARE
t integer;
BEGIN
select max(datetime) into t from dat where did=did_ and cid=0;
if t is null then
t:=0;
end if;
RETURN NEXT t;
SELECT v into t from dat where did=did_ and cid=32463 order by datetime desc limit 1;
if (not found) or (t is null) then
t:=0;
end if;
RETURN NEXT t;
END;
$$;

CREATE OR REPLACE FUNCTION JSONCURDERRINFO(d_id integer, mintime integer, maxtime integer, actionmask integer) RETURNS varchar LANGUAGE plpgsql AS $$
DECLARE
r varchar; hname varchar; x real; i integer;
BEGIN
r:='';
if (actionmask & 1)<>0 then
hname:='';
SELECT s into hname from strdat where did=d_id and cid=28624 and datetime<=maxtime order by datetime desc limit 1;
if hname is null then
hname:='';
end if;
r:=r||',"hname":'||JSONQUOTE(hname);
end if;
if (actionmask & 2)<>0 then
x:=0;
SELECT v into x from dat where did=d_id and cid=28160 and datetime<=maxtime order by datetime desc limit 1;
if x is null then
x:=0;
end if;
r:=r||',"runnum":'||trunc(x);
end if;
if (actionmask & 4)<>0 then
x:=0;
SELECT max(deep) into x from dat where did=d_id and cid=0 and datetime between mintime and maxtime;
if x is null then
x:=0;
end if;
r:=r||',"bottomdeep":'||trunc(x::numeric,2);
end if;
if (actionmask & 8)<>0 then
x:=0;
SELECT v into x from dat where did=d_id and cid=1549 and datetime<=maxtime order by datetime desc limit 1;
if x is null then
x:=0;
end if;
r:=r||',"otkl0":'||trunc(x);
end if;
if (actionmask & 16)<>0 then
x:=0;
SELECT v into x from dat where did=d_id and cid=1550 and datetime<=maxtime order by datetime desc limit 1;
if x is null then
x:=0;
end if;
r:=r||',"dOtkl":'||trunc(x);
end if;
if (actionmask & 32)<>0 then
i:=10;
SELECT op into i from dat where did=d_id and datetime<=maxtime order by datetime desc limit 1;
if i is null then
i:=10;
end if;
r:=r||',"op":'||i;
end if;
if (actionmask & 64)<>0 then -- 26.04.2014
x:=0;
SELECT v into x from dat where did=d_id and cid=32462 and datetime<=maxtime order by datetime desc limit 1;
if x is null then
x:=0;
end if;
r:=r||',"nadzab":'||trunc(x::numeric,2);
end if;
RETURN r;
END;
$$;

PREPARE
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.21 rows=1 width=28) (actual time=0.028..0.028 rows=1 loops=1)
Buffers: shared hit=7
-> Result (cost=0.01..11.76 rows=58 width=28) (actual time=0.026..0.026 rows=1 loops=1)
Buffers: shared hit=7
-> Merge Append (cost=0.01..11.76 rows=58 width=28) (actual time=0.025..0.025 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=7
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime <= 1411117512) AND (cid = 28624))
Buffers: shared hit=4
-> Index Scan Backward using strdat_134_28624 on strdat_134 strdat (cost=0.00..6.76 rows=57 width=19) (actual time=0.009..0.009 rows=1 loops=1)
Index Cond: (datetime <= 1411117512)
Filter: (did = 134)
Buffers: shared hit=3
Total runtime: 0.088 ms
(15 rows)

PREPARE
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.04..0.05 rows=1 width=0) (actual time=0.045..0.045 rows=1 loops=1)
Buffers: shared hit=6
InitPlan 1 (returns $0)
-> Limit (cost=0.01..0.04 rows=1 width=4) (actual time=0.043..0.043 rows=1 loops=1)
Buffers: shared hit=6
-> Merge Append (cost=0.01..443071.24 rows=12929831 width=4) (actual time=0.043..0.043 rows=1 loops=1)
Sort Key: public.dat.datetime
Buffers: shared hit=6
-> Index Only Scan Backward using dat_pkey on dat (cost=0.00..4.26 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime IS NOT NULL) AND (cid = 0))
Heap Fetches: 0
Buffers: shared hit=1
-> Index Scan Backward using dat_134_0 on dat_134 dat (cost=0.00..281444.08 rows=12929830 width=4) (actual time=0.033..0.033 rows=1 loops=1)
Index Cond: (datetime IS NOT NULL)
Filter: (did = 134)
Buffers: shared hit=5
Total runtime: 0.090 ms
(17 rows)

PREPARE
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.21 rows=1 width=28) (actual time=0.023..0.023 rows=1 loops=1)
Buffers: shared hit=3
-> Result (cost=0.01..11.76 rows=58 width=28) (actual time=0.020..0.020 rows=1 loops=1)
Buffers: shared hit=3
-> Merge Append (cost=0.01..11.76 rows=58 width=28) (actual time=0.020..0.020 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime <= 1411117512) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_134_28624 on strdat_134 strdat (cost=0.00..6.76 rows=57 width=19) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (datetime <= 1411117512)
Filter: (did = 134)
Buffers: shared hit=2
Total runtime: 0.072 ms
(15 rows)

PREPARE
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.04..0.05 rows=1 width=0) (actual time=0.025..0.025 rows=1 loops=1)
Buffers: shared hit=5
InitPlan 1 (returns $0)
-> Limit (cost=0.01..0.04 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)
Buffers: shared hit=5
-> Merge Append (cost=0.01..443071.24 rows=12929831 width=4) (actual time=0.023..0.023 rows=1 loops=1)
Sort Key: public.dat.datetime
Buffers: shared hit=5
-> Index Only Scan Backward using dat_pkey on dat (cost=0.00..4.26 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime IS NOT NULL) AND (cid = 0))
Heap Fetches: 0
Buffers: shared hit=1
-> Index Scan Backward using dat_134_0 on dat_134 dat (cost=0.00..281444.08 rows=12929830 width=4) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond: (datetime IS NOT NULL)
Filter: (did = 134)
Buffers: shared hit=4
Total runtime: 0.068 ms
(17 rows)

PREPARE
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.21 rows=1 width=28) (actual time=0.027..0.028 rows=1 loops=1)
Buffers: shared hit=7
-> Result (cost=0.01..11.76 rows=58 width=28) (actual time=0.025..0.025 rows=1 loops=1)
Buffers: shared hit=7
-> Merge Append (cost=0.01..11.76 rows=58 width=28) (actual time=0.025..0.025 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=7
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime <= 1411117512) AND (cid = 28624))
Buffers: shared hit=4
-> Index Scan Backward using strdat_134_28624 on strdat_134 strdat (cost=0.00..6.76 rows=57 width=19) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (datetime <= 1411117512)
Filter: (did = 134)
Buffers: shared hit=3
Total runtime: 0.083 ms
(15 rows)

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.21 rows=1 width=28) (actual time=0.019..0.019 rows=1 loops=1)
Buffers: shared hit=3
-> Result (cost=0.01..11.76 rows=58 width=28) (actual time=0.018..0.018 rows=1 loops=1)
Buffers: shared hit=3
-> Merge Append (cost=0.01..11.76 rows=58 width=28) (actual time=0.017..0.017 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime <= 1411117512) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_134_28624 on strdat_134 strdat (cost=0.00..6.76 rows=57 width=19) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (datetime <= 1411117512)
Filter: (did = 134)
Buffers: shared hit=2
Total runtime: 0.065 ms
(15 rows)

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.21 rows=1 width=28) (actual time=0.019..0.019 rows=1 loops=1)
Buffers: shared hit=3
-> Result (cost=0.01..11.76 rows=58 width=28) (actual time=0.017..0.017 rows=1 loops=1)
Buffers: shared hit=3
-> Merge Append (cost=0.01..11.76 rows=58 width=28) (actual time=0.016..0.016 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime <= 1411117512) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_134_28624 on strdat_134 strdat (cost=0.00..6.76 rows=57 width=19) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (datetime <= 1411117512)
Filter: (did = 134)
Buffers: shared hit=2
Total runtime: 0.064 ms
(15 rows)

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.19 rows=1 width=26) (actual time=0.025..0.025 rows=1 loops=1)
Buffers: shared hit=4
-> Result (cost=0.01..11.84 rows=67 width=26) (actual time=0.023..0.023 rows=1 loops=1)
Buffers: shared hit=4
-> Merge Append (cost=0.01..11.84 rows=67 width=26) (actual time=0.023..0.023 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=4
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((did = 226) AND (datetime <= 1411292950) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_226_28624 on strdat_226 strdat (cost=0.00..6.73 rows=66 width=19) (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: (datetime <= 1411292950)
Filter: (did = 226)
Buffers: shared hit=3
Total runtime: 0.067 ms
(15 rows)

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.19 rows=1 width=26) (actual time=0.015..0.016 rows=1 loops=1)
Buffers: shared hit=3
-> Result (cost=0.01..11.84 rows=67 width=26) (actual time=0.013..0.013 rows=1 loops=1)
Buffers: shared hit=3
-> Merge Append (cost=0.01..11.84 rows=67 width=26) (actual time=0.012..0.012 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((did = 226) AND (datetime <= 1411292950) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_226_28624 on strdat_226 strdat (cost=0.00..6.73 rows=66 width=19) (actual time=0.006..0.006 rows=1 loops=1)
Index Cond: (datetime <= 1411292950)
Filter: (did = 226)
Buffers: shared hit=2
Total runtime: 0.054 ms
(15 rows)

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.21 rows=1 width=28) (actual time=0.012..0.012 rows=1 loops=1)
Buffers: shared hit=3
-> Result (cost=0.01..11.76 rows=58 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Buffers: shared hit=3
-> Merge Append (cost=0.01..11.76 rows=58 width=28) (actual time=0.011..0.011 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime <= 1411117512) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_134_28624 on strdat_134 strdat (cost=0.00..6.76 rows=57 width=19) (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: (datetime <= 1411117512)
Filter: (did = 134)
Buffers: shared hit=2
Total runtime: 0.049 ms
(15 rows)

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.21 rows=1 width=28) (actual time=0.019..0.019 rows=1 loops=1)
Buffers: shared hit=3
-> Result (cost=0.01..11.76 rows=58 width=28) (actual time=0.018..0.018 rows=1 loops=1)
Buffers: shared hit=3
-> Merge Append (cost=0.01..11.76 rows=58 width=28) (actual time=0.017..0.017 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime <= 1411117512) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_134_28624 on strdat_134 strdat (cost=0.00..6.76 rows=57 width=19) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (datetime <= 1411117512)
Filter: (did = 134)
Buffers: shared hit=2
Total runtime: 0.068 ms
(15 rows)

PREPARE
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.18 rows=1 width=26) (actual time=18.807..18.807 rows=1 loops=1)
Buffers: shared hit=5 read=2
-> Result (cost=0.01..12.08 rows=73 width=26) (actual time=18.806..18.806 rows=1 loops=1)
Buffers: shared hit=5 read=2
-> Merge Append (cost=0.01..12.08 rows=73 width=26) (actual time=18.806..18.806 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=5 read=2
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.023..0.023 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime <= 1411117512) AND (cid = 28624))
Buffers: shared hit=4
-> Index Scan Backward using strdat_134_28624 on strdat_134 strdat (cost=0.00..6.90 rows=72 width=19) (actual time=18.781..18.781 rows=1 loops=1)
Index Cond: (datetime <= 1411117512)
Filter: (did = 134)
Buffers: shared hit=1 read=2
Total runtime: 18.868 ms
(15 rows)

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..2.16 rows=1 width=144) (actual time=0.046..0.047 rows=1 loops=1)
Buffers: shared hit=3 read=1
-> Result (cost=0.01..8.60 rows=4 width=144) (actual time=0.044..0.044 rows=1 loops=1)
Buffers: shared hit=3 read=1
-> Merge Append (cost=0.01..8.60 rows=4 width=144) (actual time=0.042..0.042 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3 read=1
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((did = 210) AND (datetime <= 1399399261) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_210_28624 on strdat_210 strdat (cost=0.00..4.28 rows=3 width=19) (actual time=0.036..0.036 rows=1 loops=1)
Index Cond: (datetime <= 1399399261)
Filter: (did = 210)
Buffers: shared hit=2 read=1
Total runtime: 0.094 ms
(15 rows)

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.88 rows=1 width=69) (actual time=0.032..0.032 rows=1 loops=1)
Buffers: shared hit=4
-> Result (cost=0.01..8.73 rows=10 width=69) (actual time=0.031..0.031 rows=1 loops=1)
Buffers: shared hit=4
-> Merge Append (cost=0.01..8.73 rows=10 width=69) (actual time=0.029..0.029 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=4
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((did = 223) AND (datetime <= 1410961708) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_223_28624 on strdat_223 strdat (cost=0.00..4.33 rows=9 width=19) (actual time=0.024..0.024 rows=1 loops=1)
Index Cond: (datetime <= 1410961708)
Filter: (did = 223)
Buffers: shared hit=3
Total runtime: 0.077 ms
(15 rows)

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2.27..2.27 rows=1 width=270) (actual time=227.332..227.333 rows=1 loops=1)
Buffers: shared hit=1 read=18
-> Sort (cost=2.27..2.27 rows=2 width=270) (actual time=227.330..227.330 rows=1 loops=1)
Sort Key: public.strdat.datetime
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=1 read=18
-> Result (cost=0.00..2.26 rows=2 width=270) (actual time=16.240..227.257 rows=29 loops=1)
Buffers: shared hit=1 read=18
-> Append (cost=0.00..2.26 rows=2 width=270) (actual time=16.239..227.241 rows=29 loops=1)
Buffers: shared hit=1 read=18
-> Seq Scan on strdat (cost=0.00..0.00 rows=1 width=520) (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((datetime <= 1411292950) AND (did = 226) AND (cid = 28624))
-> Index Scan Backward using strdat_226_28624 on strdat_226 strdat (cost=0.00..2.26 rows=1 width=19) (actual time=16.237..227.226 rows=29 loops=1)
Index Cond: (datetime <= 1411292950)
Filter: (did = 226)
Buffers: shared hit=1 read=18
Total runtime: 227.369 ms
(17 rows)

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.18 rows=1 width=26) (actual time=0.015..0.015 rows=1 loops=1)
Buffers: shared hit=3
-> Result (cost=0.01..12.08 rows=73 width=26) (actual time=0.014..0.014 rows=1 loops=1)
Buffers: shared hit=3
-> Merge Append (cost=0.01..12.08 rows=73 width=26) (actual time=0.014..0.014 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((did = 134) AND (datetime <= 1411117512) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_134_28624 on strdat_134 strdat (cost=0.00..6.90 rows=72 width=19) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (datetime <= 1411117512)
Filter: (did = 134)
Buffers: shared hit=2
Total runtime: 0.042 ms
(15 rows)

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..2.16 rows=1 width=144) (actual time=0.010..0.011 rows=1 loops=1)
Buffers: shared hit=3
-> Result (cost=0.01..8.60 rows=4 width=144) (actual time=0.009..0.009 rows=1 loops=1)
Buffers: shared hit=3
-> Merge Append (cost=0.01..8.60 rows=4 width=144) (actual time=0.009..0.009 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((did = 210) AND (datetime <= 1399399261) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_210_28624 on strdat_210 strdat (cost=0.00..4.28 rows=3 width=19) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (datetime <= 1399399261)
Filter: (did = 210)
Buffers: shared hit=2
Total runtime: 0.048 ms
(15 rows)

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.01..0.88 rows=1 width=69) (actual time=0.010..0.010 rows=1 loops=1)
Buffers: shared hit=3
-> Result (cost=0.01..8.73 rows=10 width=69) (actual time=0.009..0.009 rows=1 loops=1)
Buffers: shared hit=3
-> Merge Append (cost=0.01..8.73 rows=10 width=69) (actual time=0.009..0.009 rows=1 loops=1)
Sort Key: public.strdat.datetime
Buffers: shared hit=3
-> Index Scan Backward using strdat_pkey on strdat (cost=0.00..4.26 rows=1 width=520) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: ((did = 223) AND (datetime <= 1410961708) AND (cid = 28624))
Buffers: shared hit=1
-> Index Scan Backward using strdat_223_28624 on strdat_223 strdat (cost=0.00..4.33 rows=9 width=19) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (datetime <= 1410961708)
Filter: (did = 223)
Buffers: shared hit=2
Total runtime: 0.034 ms
(15 rows)

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2.27..2.27 rows=1 width=270) (actual time=0.035..0.036 rows=1 loops=1)
Buffers: shared hit=18
-> Sort (cost=2.27..2.27 rows=2 width=270) (actual time=0.035..0.035 rows=1 loops=1)
Sort Key: public.strdat.datetime
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=18
-> Result (cost=0.00..2.26 rows=2 width=270) (actual time=0.005..0.028 rows=29 loops=1)
Buffers: shared hit=18
-> Append (cost=0.00..2.26 rows=2 width=270) (actual time=0.005..0.027 rows=29 loops=1)
Buffers: shared hit=18
-> Seq Scan on strdat (cost=0.00..0.00 rows=1 width=520) (actual time=0.000..0.000 rows=0 loops=1)
Filter: ((datetime <= 1411292950) AND (did = 226) AND (cid = 28624))
-> Index Scan Backward using strdat_226_28624 on strdat_226 strdat (cost=0.00..2.26 rows=1 width=19) (actual time=0.004..0.023 rows=29 loops=1)
Index Cond: (datetime <= 1411292950)
Filter: (did = 226)
Buffers: shared hit=18
Total runtime: 0.056 ms
(17 rows)

ts_age state query_age change_age datname pid usename waiting client_addr client_port query
01:15:11.548306 active 01:15:11.548306 01:15:11.548306 postgres 13510 postgres 0
-1 select GetBottomInfo(201)

ID Владелец Использование CPU Команда
13510 postgres 4.9 % postgres: postgres postgres [local] SELECT

autovacuum = on
#log_autovacuum_min_duration = -1
autovacuum_max_workers = 4
autovacuum_naptime = 5s # Сброс в базу в приложении каждые 5 секунд, поэтому. Хотя, может неправильно.
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 10
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.02
#autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = 10ms
#autovacuum_vacuum_cost_limit = -1