Summary:
history.get sub-period fetching (ZBX-27803, 7.0.32) causes multi-chunk full scans on TimescaleDB for sparsely collected items (Item history widget), saturating the database server

Affects version: 7.0.32
Component: Frontend (F), API (A)

Steps to reproduce:

1. Environment: Zabbix 7.0.32, PostgreSQL 17.5, TimescaleDB 2.18.2, compression of chunks older than 7d, hk_history_global=1 with 60d),uncompressed chunk about 13 GB, compressed about 800 MB. Database host: 4 vCPU, 64 GB RAM, shared_buffers about 16 GB. 
2. Create six numeric (unsigned) items that receive exactly one value per day, e.g. calculated items with the scheduling interval 0;h23m55 (daily totals computed from log.count). Let them collect for at least two weeks. In any 7-day window these six items have 42 rows in history_uint in total.
3. Dashboards -> create an Item history widget: Layout = Vertical, the six items as columns, Show lines = 12, Advanced configuration -> Time period = Custom, From = now-12d, To = now. (This exact configuration has been in production unchanged since 2025-11 and worked on previous 7.0.x versions.)
4. Open the dashboard in a browser and watch pg_stat_activity on the database server.

Result:

The widget calls history.get with limit = show_lines * count(items) = 72, sortfield = [clock, ns], sortorder = DESC, time_from = now-12d, time_till = now 

Since 7.0.32, CHistory::getFromSqlUsingTimeBasedChunking (introduced by ZBX-27803) splits the request into sub-period queries walking backwards from time_till with window sizes of 1 hour, 1 day, 7 days and 30 days, each with LIMIT = 72 minus the rows already collected. For the sparse items this yields:
1. [T-1h, T]: 0 rows
2. [T-25h, T-1h]: 6 rows (yesterday's values)
3. [T-8d-1h, T-25h]: LIMIT 66, 7-day window, never finishes (see below)
4. [time_from, T-8d-1h]: never reached

Sub-period 3 as seen in pg_stat_activity:

SELECT h.itemid,h.value,h.clock,h.ns FROM history_uint h
WHERE h.itemid IN (335670,335671,1046526,1069515,1069516,1069517)
  AND h.clock>=1792044603 AND h.clock<=1792649402
ORDER BY h.clock DESC,h.ns DESC LIMIT 66

EXPLAIN of this query with default settings:

Limit  (cost=690.40..46220.65 rows=66 width=21)
  ->  Incremental Sort  (cost=690.40..31117165.45 rows=45106 width=21)
        Sort Key: h.clock DESC, h.ns DESC
        Presorted Key: h.clock
        ->  Custom Scan (ChunkAppend) on history_uint h  (cost=0.57..31115135.68 rows=45106 width=21)
              Order: h.clock DESC
              ->  Index Scan using _hyper_2_11862_chunk_history_uint_clock_idx on _hyper_2_11862_chunk h_1  (cost=0.57..1230365.70 rows=1693 width=21)
                    Index Cond: ((clock >= 1792045473) AND (clock <= 1792650272))
                    Filter: (itemid = ANY ('{335670,335671,1046526,1069515,1069516,1069517}'::bigint[]))
              ->  Index Scan using _hyper_2_11852_chunk_history_uint_clock_idx on _hyper_2_11852_chunk h_2  (cost=0.57..4496879.89 rows=6461 width=21)
                    Index Cond: ((clock >= 1792045473) AND (clock <= 1792650272))
                    Filter: (itemid = ANY ('{335670,335671,1046526,1069515,1069516,1069517}'::bigint[]))
              ->  Index Scan using _hyper_2_11842_chunk_history_uint_clock_idx on _hyper_2_11842_chunk h_3  (cost=0.57..4471551.55 rows=6408 width=21)
                    (same Index Cond and Filter)
              ->  Index Scan using _hyper_2_11832_chunk_history_uint_clock_idx on _hyper_2_11832_chunk h_4  (cost=0.57..4461408.54 rows=6420 width=21)
              ->  Index Scan using _hyper_2_11822_chunk_history_uint_clock_idx on _hyper_2_11822_chunk h_5  (cost=0.57..4441508.70 rows=6650 width=21)
              ->  Index Scan using _hyper_2_11812_chunk_history_uint_clock_idx on _hyper_2_11812_chunk h_6  (cost=0.57..4441984.52 rows=6494 width=21)
              ->  Index Scan using _hyper_2_11802_chunk_history_uint_clock_idx on _hyper_2_11802_chunk h_7  (cost=0.57..4437438.32 rows=6381 width=21)
              ->  Index Scan using _hyper_2_11790_chunk_history_uint_clock_idx on _hyper_2_11790_chunk h_8  (cost=0.57..3133998.46 rows=4599 width=21)

The planner chooses an ordered ChunkAppend over the TimescaleDB default per-chunk index history_uint_clock_idx (created by create_hypertable with create_default_indexes = true in timescaledb/schema.sql), with itemid only as a Filter, followed by Incremental Sort and Limit. It estimates 45,106 matching rows (average item density), but the six sparse items have only 42 rows in the window. LIMIT 66 is therefore never satisfied, the early exit never happens, and the query reads all 8 uncompressed chunks of the window, about 100 GB, one backend per request. This plan wins only by a small margin (46,220 vs 52,578 for the primary-key plan) and only when the sub-period spans about 7 or more uncompressed chunks; the 1-day sub-period still uses the primary key.

Measured: in a test session the query was cancelled by a 30 s statement_timeout; in production the copies ran for more than 25 minutes under load without finishing.

Impact: the web server times out after 180 s, the dashboard JavaScript retries 3 s later, and the PHP process and the database backend keep running, so each dashboard viewer adds a new multi-chunk scan every 183 s. We observed 19 concurrent copies from two frontend nodes, 1 % idle CPU, 26 % iowait, 220 MB/s reads at 99 % disk utilisation, swap in use, and the Zabbix server competing for the same disk. Changing the widget refresh interval does not help, because every page load and every retry triggers the same request.

Before 7.0.32 the widget issued a single query over the whole 12-day period and the problem did not occur.

Expected:

history.get with a limit and an explicit time_from should not scan whole chunks for items that have few values in the period. The request should complete in milliseconds through the (itemid, clock, ns) primary key, which is exactly what happens once the planner cannot use Incremental Sort:

EXPLAIN of the same query with enable_incremental_sort = off:

Limit  (cost=52577.88..52578.05 rows=66 width=21)
  ->  Sort  (cost=52577.88..52690.65 rows=45106 width=21)
        Sort Key: h_1.clock DESC, h_1.ns DESC
        ->  Append  (cost=0.57..50989.16 rows=45106 width=21)
              ->  Index Scan using "11862_6030_history_uint_pkey" on _hyper_2_11862_chunk h_1  (cost=0.57..1915.85 rows=1693 width=21)
                    Index Cond: ((itemid = ANY ('{335670,335671,1046526,1069515,1069516,1069517}'::bigint[])) AND (clock >= 1792045473) AND (clock <= 1792650272))
              ->  Index Scan using "11852_6025_history_uint_pkey" on _hyper_2_11852_chunk h_2  (cost=0.57..7267.08 rows=6461 width=21)
              ->  Index Scan using "11842_6020_history_uint_pkey" on _hyper_2_11842_chunk h_3  (cost=0.57..7209.58 rows=6408 width=21)
              ->  Index Scan using "11832_6015_history_uint_pkey" on _hyper_2_11832_chunk h_4  (cost=0.57..7222.83 rows=6420 width=21)
              ->  Index Scan using "11822_6010_history_uint_pkey" on _hyper_2_11822_chunk h_5  (cost=0.57..7478.87 rows=6650 width=21)
              ->  Index Scan using "11812_6005_history_uint_pkey" on _hyper_2_11812_chunk h_6  (cost=0.57..7304.92 rows=6494 width=21)
              ->  Index Scan using "11802_6000_history_uint_pkey" on _hyper_2_11802_chunk h_7  (cost=0.57..7179.12 rows=6381 width=21)
              ->  Index Scan using "11790_5994_history_uint_pkey" on _hyper_2_11790_chunk h_8  (cost=0.57..5185.38 rows=4599 width=21)
                    (same Index Cond on every chunk)

Measured with enable_incremental_sort = off: 42 rows in 0.109 s wall time including psql start-up.

Workaround applied in production: ALTER DATABASE <zabbix db> SET enable_incremental_sort = off; This removes the only path by which the clock-index scan can look cheap for ORDER BY clock DESC, ns DESC LIMIT n, so all sub-period queries use the primary key. Dropping the TimescaleDB default history*_clock_idx indexes would also fix it, but we have not changed the schema.

Requests:
1. Please confirm this as a regression of ZBX-27803 for sparsely collected items on TimescaleDB and consider a fix, for example: skip the sub-period logic when the caller supplies time_from (the original ZBX-27803 problem was history.get without time_from), fetch per item in (itemid, clock) order as CHistoryManager::getLastValues does, or stop widening the windows once a sub-period returns far fewer rows than requested.
2. Is dropping the TimescaleDB default clock indexes (history_clock_idx, history_uint_clock_idx, history_str_clock_idx, history_log_clock_idx, history_text_clock_idx, history_bin_clock_idx) a supported change? Should timescaledb/schema.sql pass create_default_indexes => false? Zabbix itself only uses the (itemid, clock, ns) primary key on these tables.
3. Is enable_incremental_sort = off on the Zabbix database an acceptable supported workaround until a fix is released?