[ZBX-12104] Slow query in items.inc.php/item_get_history Created: 2017 Apr 25  Updated: 2024 Apr 10  Resolved: 2018 Jun 29

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: API (A)
Affects Version/s: 3.2.4
Fix Version/s: 3.0.20rc1, 3.4.12rc1, 4.0.0alpha9, 4.0 (plan)

Type: Problem report Priority: Major
Reporter: Peter Gervai Assignee: Gregory Chalenko
Resolution: Fixed Votes: 0
Labels: patch, postgresql, query, slow, sql
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Debian/jessie

Attachments: File items.inc.php.diff    
Issue Links:
Duplicate
duplicates ZBX-12066 Timeline slider initialization slow S... Closed
Team: Team D
Team: Team D
Sprint: Sprint 33, Sprint 34, Sprint 35, Sprint 36, Sprint 37
Story Points: 1

 Description   

Postgresql partitioned db is slow as hell on a hot summer day. Seems these queries take forever plus one day:

SELECT MAX(clock) AS clock FROM history WHERE itemid='135968' AND clock <= 1482098389;

No matter how to index, this will seq scan all the indexes, and all the partitioned tables in the past (since clock will not limit the search without the lower bound, and the table is partitioned based on clock).

Limiting the lookback (arbitrarily picked 3days, one day would be as good) makes it great again. See attachment.

 Comments   
Comment by Peter Gervai [ 2017 Apr 25 ]

Feel free to mutate it however you please. Works for me, but you may transpose it to match the greater good.

Comment by Marc [ 2017 Apr 25 ]

Cannot confirm/reproduce this observation. Can you share the execution plan of the concerned SQL statement, please? (EXPLAIN ANALYZE)

Comment by Marc [ 2017 May 02 ]

Just for information, here two examples of how the mentioned query performs on our Postgres 9.3 cluster.
The first for a rather old clock value and the second with a pretty recent value.

One can clearly see that check constraints are limiting the partitions to look at to a minimum. However, an execution time (incl. analyze overhead) of less than 2 ms looks not "slow" to me.

PS: The average size of a single history partition is 4.6 GB.

zabbix=# explain analyze SELECT MAX(clock) AS clock FROM history WHERE itemid='23257' AND clock <= 1488672000;
                                                                                            QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------
 Result  (cost=5.65..5.66 rows=1 width=0) (actual time=0.025..0.025 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=1.58..5.65 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)
           ->  Merge Append  (cost=1.58..5779.67 rows=1422 width=4) (actual time=0.023..0.023 rows=1 loops=1)
                 Sort Key: history.clock
                 ->  Index Only Scan Backward using history_1 on history  (cost=0.43..4.45 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1488672000))
                       Heap Fetches: 0
                 ->  Index Only Scan Backward using history_p1485302400_itemid_clock_idx on history_p1485302400  (cost=0.56..5740.51 rows=1420 width=4) (actual time=0.0
12..0.012 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1488672000))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1488672000_itemid_clock_idx on history_p1488672000  (cost=0.56..8.59 rows=1 width=4) (actual time=0.004..0.
004 rows=0 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1488672000))
                       Heap Fetches: 0
 Total runtime: 0.059 ms
(15 rows)

zabbix=#

zabbix=# explain analyze SELECT MAX(clock) AS clock FROM history WHERE itemid='23257' AND clock <= 1493725877;
                                                                                            QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------
 Result  (cost=40.24..40.25 rows=1 width=0) (actual time=0.965..0.965 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=36.14..40.24 rows=1 width=4) (actual time=0.962..0.962 rows=1 loops=1)
           ->  Merge Append  (cost=36.14..346904.26 rows=84560 width=4) (actual time=0.960..0.960 rows=1 loops=1)
                 Sort Key: history.clock
                 ->  Index Only Scan Backward using history_1 on history  (cost=0.43..5.92 rows=66 width=4) (actual time=0.016..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 0
                 ->  Index Only Scan Backward using history_p1485302400_itemid_clock_idx on history_p1485302400  (cost=0.56..5740.51 rows=1420 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1488672000_itemid_clock_idx on history_p1488672000  (cost=0.56..5680.18 rows=1405 width=4) (actual time=0.0
18..0.018 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1488758400_itemid_clock_idx on history_p1488758400  (cost=0.56..5696.22 rows=1409 width=4) (actual time=0.0
17..0.017 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1488844800_itemid_clock_idx on history_p1488844800  (cost=0.56..5603.55 rows=1386 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1488931200_itemid_clock_idx on history_p1488931200  (cost=0.56..5523.73 rows=1368 width=4) (actual time=0.0
13..0.013 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489017600_itemid_clock_idx on history_p1489017600  (cost=0.56..5579.61 rows=1380 width=4) (actual time=0.0
14..0.014 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489104000_itemid_clock_idx on history_p1489104000  (cost=0.56..5652.02 rows=1398 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489190400_itemid_clock_idx on history_p1489190400  (cost=0.56..5708.31 rows=1412 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489276800_itemid_clock_idx on history_p1489276800  (cost=0.56..5651.61 rows=1398 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489363200_itemid_clock_idx on history_p1489363200  (cost=0.56..5547.30 rows=1372 width=4) (actual time=0.0
13..0.013 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489449600_itemid_clock_idx on history_p1489449600  (cost=0.56..5656.04 rows=1399 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489536000_itemid_clock_idx on history_p1489536000  (cost=0.56..5668.08 rows=1402 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489622400_itemid_clock_idx on history_p1489622400  (cost=0.56..5611.70 rows=1388 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489708800_itemid_clock_idx on history_p1489708800  (cost=0.56..5675.96 rows=1404 width=4) (actual time=0.0
17..0.017 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489795200_itemid_clock_idx on history_p1489795200  (cost=0.56..5607.75 rows=1387 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489881600_itemid_clock_idx on history_p1489881600  (cost=0.56..5659.79 rows=1400 width=4) (actual time=0.0
17..0.017 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1489968000_itemid_clock_idx on history_p1489968000  (cost=0.56..5611.42 rows=1388 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490054400_itemid_clock_idx on history_p1490054400  (cost=0.56..5623.85 rows=1391 width=4) (actual time=0.0
13..0.013 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490140800_itemid_clock_idx on history_p1490140800  (cost=0.56..5684.10 rows=1406 width=4) (actual time=0.0
17..0.017 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490227200_itemid_clock_idx on history_p1490227200  (cost=0.56..5651.83 rows=1398 width=4) (actual time=0.0
13..0.013 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490313600_itemid_clock_idx on history_p1490313600  (cost=0.56..5603.66 rows=1386 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490400000_itemid_clock_idx on history_p1490400000  (cost=0.56..5641.08 rows=1397 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490486400_itemid_clock_idx on history_p1490486400  (cost=0.56..5418.71 rows=1340 width=4) (actual time=0.0
17..0.017 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490572800_itemid_clock_idx on history_p1490572800  (cost=0.56..5672.11 rows=1403 width=4) (actual time=0.0
14..0.014 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490659200_itemid_clock_idx on history_p1490659200  (cost=0.56..5703.83 rows=1411 width=4) (actual time=0.0
17..0.017 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490745600_itemid_clock_idx on history_p1490745600  (cost=0.56..5655.98 rows=1399 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490832000_itemid_clock_idx on history_p1490832000  (cost=0.56..5611.79 rows=1388 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1490918400_itemid_clock_idx on history_p1490918400  (cost=0.56..5748.41 rows=1422 width=4) (actual time=0.0
21..0.021 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491004800_itemid_clock_idx on history_p1491004800  (cost=0.56..5374.44 rows=1328 width=4) (actual time=0.0
23..0.023 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491091200_itemid_clock_idx on history_p1491091200  (cost=0.56..9956.07 rows=2467 width=4) (actual time=0.0
21..0.021 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491177600_itemid_clock_idx on history_p1491177600  (cost=0.56..5354.33 rows=1323 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491264000_itemid_clock_idx on history_p1491264000  (cost=0.56..5362.36 rows=1325 width=4) (actual time=0.0
13..0.013 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491350400_itemid_clock_idx on history_p1491350400  (cost=0.56..5354.30 rows=1323 width=4) (actual time=0.0
18..0.018 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491436800_itemid_clock_idx on history_p1491436800  (cost=0.56..5350.22 rows=1322 width=4) (actual time=0.0
17..0.017 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491523200_itemid_clock_idx on history_p1491523200  (cost=0.56..9384.80 rows=2325 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491609600_itemid_clock_idx on history_p1491609600  (cost=0.56..5378.46 rows=1329 width=4) (actual time=0.0
12..0.012 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491696000_itemid_clock_idx on history_p1491696000  (cost=0.56..5233.63 rows=1294 width=4) (actual time=0.0
11..0.011 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491782400_itemid_clock_idx on history_p1491782400  (cost=0.56..5171.31 rows=1280 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491868800_itemid_clock_idx on history_p1491868800  (cost=0.56..5301.13 rows=1310 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1491955200_itemid_clock_idx on history_p1491955200  (cost=0.56..5366.40 rows=1326 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492041600_itemid_clock_idx on history_p1492041600  (cost=0.56..5382.49 rows=1330 width=4) (actual time=0.0
18..0.018 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492128000_itemid_clock_idx on history_p1492128000  (cost=0.56..5378.46 rows=1329 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492214400_itemid_clock_idx on history_p1492214400  (cost=0.56..5378.46 rows=1329 width=4) (actual time=0.0
17..0.017 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492300800_itemid_clock_idx on history_p1492300800  (cost=0.56..5366.40 rows=1326 width=4) (actual time=0.0
18..0.018 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492387200_itemid_clock_idx on history_p1492387200  (cost=0.56..5378.47 rows=1329 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492473600_itemid_clock_idx on history_p1492473600  (cost=0.56..9421.08 rows=2334 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492560000_itemid_clock_idx on history_p1492560000  (cost=0.56..5374.42 rows=1328 width=4) (actual time=0.0
14..0.014 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492646400_itemid_clock_idx on history_p1492646400  (cost=0.56..5374.44 rows=1328 width=4) (actual time=0.0
14..0.014 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492732800_itemid_clock_idx on history_p1492732800  (cost=0.56..5370.42 rows=1327 width=4) (actual time=0.0
12..0.012 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492819200_itemid_clock_idx on history_p1492819200  (cost=0.56..5390.53 rows=1332 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492905600_itemid_clock_idx on history_p1492905600  (cost=0.56..5378.47 rows=1329 width=4) (actual time=0.0
12..0.012 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1492992000_itemid_clock_idx on history_p1492992000  (cost=0.56..5370.41 rows=1327 width=4) (actual time=0.0
14..0.014 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1493078400_itemid_clock_idx on history_p1493078400  (cost=0.56..5354.33 rows=1323 width=4) (actual time=0.0
15..0.015 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1493164800_itemid_clock_idx on history_p1493164800  (cost=0.56..5370.38 rows=1327 width=4) (actual time=0.0
13..0.013 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1493251200_itemid_clock_idx on history_p1493251200  (cost=0.56..5374.44 rows=1328 width=4) (actual time=0.0
19..0.019 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1493337600_itemid_clock_idx on history_p1493337600  (cost=0.56..5366.39 rows=1326 width=4) (actual time=0.0
13..0.013 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1493424000_itemid_clock_idx on history_p1493424000  (cost=0.56..5374.42 rows=1328 width=4) (actual time=0.0
14..0.014 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1493510400_itemid_clock_idx on history_p1493510400  (cost=0.56..5378.45 rows=1329 width=4) (actual time=0.0
12..0.012 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1493596800_itemid_clock_idx on history_p1493596800  (cost=0.56..5378.43 rows=1329 width=4) (actual time=0.0
12..0.012 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
                 ->  Index Only Scan Backward using history_p1493683200_itemid_clock_idx on history_p1493683200  (cost=0.56..5032.61 rows=1247 width=4) (actual time=0.0
16..0.016 rows=1 loops=1)
                       Index Cond: ((itemid = 23257::bigint) AND (clock IS NOT NULL) AND (clock <= 1493725877))
                       Heap Fetches: 1
 Total runtime: 1.847 ms
(189 rows)
Comment by Peter Gervai [ 2017 May 04 ]

The bounded time query runs around 20000 msec. (20 sec for the laymen)
The query without lower bound runs approximately 14000000 ms. (thats about 4 hours)

I will try to create a planner run later because the server is being fiddled right now. What I've seen that it had to read all partitions before the given time and full index scans took plenty of time. Partitions are about 3 GB, as well as the indexes (so probably reading index instead of a seq scan doesn't buy much).

(Tried to play with indexes and index orders but there are massive amounts of itemids

Comment by Peter Gervai [ 2017 May 04 ]

Just explaining a bounded query:

zabbix=> explain  SELECT MAX(clock) AS clock FROM history_backup WHERE itemid='74960' AND clock BETWEEN 1488122000 AND 1488672000; 
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=383.34..383.35 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=1.17..383.34 rows=1 width=4)
           ->  Merge Append  (cost=1.17..2364876.72 rows=6188 width=4)
                 Sort Key: history_backup.clock
                 ->  Sort  (cost=0.01..0.02 rows=1 width=4)
                       Sort Key: history_backup.clock
                       ->  Seq Scan on history_backup  (cost=0.00..0.00 rows=1 width=4)
                             Filter: ((clock IS NOT NULL) AND (clock >= 1488122000) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1708 on h_1708  (cost=0.57..104904.24 rows=273 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock >= 1488122000) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1709 on h_1709  (cost=0.57..2259858.89 rows=5914 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock >= 1488122000) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
(13 rows)

And unbounded:

explain  SELECT MAX(clock) AS clock FROM history_backup WHERE itemid='74960' AND clock<=1488672000; 

                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Result  (cost=1279.56..1279.57 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=9.93..1279.56 rows=1 width=4)
           ->  Merge Append  (cost=9.93..91834109.76 rows=72331 width=4)
                 Sort Key: history_backup.clock
                 ->  Sort  (cost=0.01..0.02 rows=1 width=4)
                       Sort Key: history_backup.clock
                       ->  Seq Scan on history_backup  (cost=0.00..0.00 rows=1 width=4)
                             Filter: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1640 on h_1640  (cost=0.15..17.43 rows=2 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1641 on h_1641  (cost=0.15..17.43 rows=2 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1642 on h_1642  (cost=0.15..17.43 rows=2 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1643 on h_1643  (cost=0.15..17.43 rows=2 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1644 on h_1644  (cost=0.15..17.43 rows=2 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1645 on h_1645  (cost=0.15..17.43 rows=2 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1646 on h_1646  (cost=0.15..17.43 rows=2 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1647 on h_1647  (cost=0.43..87403.66 rows=294 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1648 on h_1648  (cost=0.56..1028639.07 rows=4046 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1649 on h_1649  (cost=0.56..1072702.97 rows=4236 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Scan Backward using hidx_1650_clock on h_1650  (cost=0.56..70533202.75 rows=4288 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000))
                       Filter: (itemid = 74960::bigint)
                 ->  Index Scan Backward using hidx_1651_clock on h_1651  (cost=0.56..1274296.74 rows=4470 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000))
                       Filter: (itemid = 74960::bigint)
                 ->  Index Only Scan Backward using hidx_1652 on h_1652  (cost=0.56..1139710.03 rows=4571 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1701 on h_1701  (cost=0.56..1202280.49 rows=4501 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1702 on h_1702  (cost=0.56..1214205.60 rows=4606 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1703 on h_1703  (cost=0.56..1386785.70 rows=4706 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1704 on h_1704  (cost=0.56..1695554.12 rows=5128 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1705 on h_1705  (cost=0.56..1934757.87 rows=5440 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1706 on h_1706  (cost=0.56..2391773.97 rows=6691 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1707 on h_1707  (cost=0.57..2359473.15 rows=6715 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1708 on h_1708  (cost=0.57..2401862.15 rows=6710 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
                 ->  Index Only Scan Backward using hidx_1709 on h_1709  (cost=0.57..2107886.13 rows=5914 width=4)
                       Index Cond: ((clock IS NOT NULL) AND (clock <= 1488672000) AND (itemid = 74960::bigint))
(55 rows)

The planner was pretty optimistic last time and it took much longer, but you probably see what I mean even without analyse. But if you insist I could try to ANALYSE them, and back with the results in a few DAYS :-P.

Comment by Peter Gervai [ 2017 May 04 ]

Right now the differences (cached version):

 Planning time: 4.829 ms
 Execution time: 55.269 ms

versus

 Planning time: 3.329 ms
 Execution time: 1505.889 ms

But if the data is not cached these should be multiplied by 1000 or more.

Comment by Alexander Vladishev [ 2018 Jun 22 ]

(2) [D] Documentation needs to be updated - {ITEM.VALUE} macro will take into account the option ZBX_HISTORY_PERIOD

  • 3.0.20 and 3.4.12 upgrade notes
  • 3.0, 3.4 and 4.0 definitions page

martins-v Thanks, gcalenko, for adding the info to the definition page. I've also added it to the upgrade notes for 3.0.20 and 3.4.12. RESOLVED

Generated at Fri Apr 19 10:45:48 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.