[Bug]: Query in TimescaleDB 2.20.1 runs in 60 ms, but in TimescaleDB 2.29.2 it fails after 15 min
Author: igor2xCreated Sep 14, 2026Updated Sep 17, 2026
Labelsbugv2.29.2
What type of bug is this?
Performance issue
What subsystems and features are affected?
Compression
What happened?
We have created a clone of our production database and on cloned test system upgraded TimescaleDB software from TimescaleDB 2.20.1 to TimescaleDB 2.29.2 and one of the query that previously run for 60 miliseconds now fails after 15 min (probably because we have statement_timeout=15min set).
TimescaleDB version affected
2.29.2
PostgreSQL version used
18.6
What operating system did you use?
Red Hat 9.8
What installation method did you use?
RPM
What platform did you run on?
On prem/Self-hosted
How can we reproduce the bug?
Current production system:
- Red Hat 9.6
- PostgreSQL 15.13
- TimescaleDB 2.20.1
Current test system:
- Red Hat 9.8
- PostgreSQL 18.6
- TimescaleDB 2.29.2 (we started testing at second half of August)
In order to give green light on migration we are required to do a lot of tests that takes few weeks to complete. So far everything looks great expect one single query that in production system is executed in 60 ms, but on test system query fails after 15 min, because we have statement_timeout=15min set, so queries like this to not kill the server.
Recap: From production database explain I see chunks are first filtered by index and only single row per chunk returned. But in the test system explain expects to get 13 million rows per every chunk and does the sequential scan on each of chunk that has 100 million rows and this is probably the reason for performance issue.
The query is the following:
select * from prod.v_merilni_podatki_energija_15_min_sumarna vmpems
where dis=2 and
vmpems.enotni_ident_mm ='2-7156' and
vmpems.casovna_znacka >'2025-05-01' and
vmpems.casovna_znacka <='2025-06-01';but query is accessing the following view. See: [view_definition.txt
To simplify testing I have merged "view" and "query" into single query for testing purposes, so for every test I perform I don't need to drop/create views. See: merged_query.txt
- Explain on production for this "merged query" with "explain (analyze, buffers)". See: explain_in_production.txt.
It takes only 68 milliseconds query to finish.
- On test system I can't use "explain with analyze", because query does not finish in 15 min. I executed "explain (buffers)":
Result:
GroupAggregate (cost=12435413.24..12435543.28 rows=200 width=165)
Group Key: po_pravilih.casovna_znacka
-> Sort (cost=12435413.24..12435418.72 rows=2192 width=174)
Sort Key: po_pravilih.casovna_znacka
-> Subquery Scan on po_pravilih (cost=14083.87..12435291.61 rows=2192 width=174)
-> GroupAggregate (cost=14083.87..12435269.69 rows=2192 width=185)
Group Key: smc.id_sumarna_mm_clen_pravila, mpe.casovna_znacka, smcp.funkcija, smf.register, smf.faktor
-> Incremental Sort (cost=14083.87..12435105.29 rows=2192 width=82)
Sort Key: smc.id_sumarna_mm_clen_pravila, mpe.casovna_znacka, smcp.funkcija, smf.register, smf.faktor
Presorted Key: smc.id_sumarna_mm_clen_pravila
-> Nested Loop (cost=1009.16..12435045.67 rows=2192 width=82)
Join Filter: (smf.id_sumarna_mm_clen_pravila = smcp.id_sumarna_mm_clen_pravila)
-> Index Scan using t_sumarna_mm_clen_pravila_pkey on t_sumarna_mm_clen_pravila smcp (cost=0.28..25.14 rows=951 width=8)
-> Materialize (cost=1008.88..12403757.13 rows=2192 width=82)
-> Gather (cost=1008.88..12403746.17 rows=2192 width=82)
Workers Planned: 7
-> Hash Join (cost=8.88..12402526.97 rows=313 width=82)
Hash Cond: ((mpe.dis = smc.dis) AND ((mpe.enotni_ident_mm)::text = (smc.enotni_ident_mm)::text))
Join Filter: ((smfc.casovna_znacka_od <= mpe.casovna_znacka) AND (smfc.casovna_znacka_do >= mpe.casovna_znacka))
-> Parallel Append (cost=1.67..9286856.65 rows=415416111 width=51)
-> Custom Scan (ColumnarScan) on _hyper_21_37651_chunk mpe_32 (cost=357.64..16432.17 rows=4400 width=53)
Vectorized Filter: ((casovna_znacka > '2025-05-01 00:00:00+02'::timestamp with time zone) AND (casovna_znacka <= '2025-06-01 00:00:00+02'::timestamp with time zone))
-> Parallel Index Scan using compress_hyper_24_40159_chunk_dis_enotni_ident_mm__ts_meta__idx on compress_hyper_24_40159_chunk (cost=0.42..16388.17 rows=46 width=350)
Index Cond: ((_ts_meta_min_1 <= '2025-06-01 00:00:00+02'::timestamp with time zone) AND (_ts_meta_max_1 > '2025-05-01 00:00:00+02'::timestamp with time zone))
-> Custom Scan (ColumnarScan) on _hyper_21_37595_chunk mpe_5 (cost=1.67..233265.99 rows=13409493 width=52)
-> Parallel Seq Scan on compress_hyper_24_40118_chunk (cost=0.00..99171.06 rows=139706 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37591_chunk mpe_3 (cost=1.67..233034.04 rows=13405744 width=52)
-> Parallel Seq Scan on compress_hyper_24_40116_chunk (cost=0.00..98976.60 rows=139660 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37596_chunk mpe_6 (cost=1.67..232960.97 rows=13411775 width=52)
-> Parallel Seq Scan on compress_hyper_24_40119_chunk (cost=0.00..98843.22 rows=139722 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37593_chunk mpe_4 (cost=1.67..232823.11 rows=13407827 width=52)
-> Parallel Seq Scan on compress_hyper_24_40117_chunk (cost=0.00..98744.83 rows=139683 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37612_chunk mpe_14 (cost=1.67..232927.63 rows=13418484 width=51)
-> Parallel Seq Scan on compress_hyper_24_40144_chunk (cost=0.00..98742.79 rows=139779 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37618_chunk mpe_17 (cost=1.67..232899.19 rows=13418244 width=51)
-> Parallel Seq Scan on compress_hyper_24_40147_chunk (cost=0.00..98716.75 rows=139775 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37609_chunk mpe_12 (cost=1.67..232848.71 rows=13416698 width=51)
-> Parallel Seq Scan on compress_hyper_24_40135_chunk (cost=0.00..98681.74 rows=139774 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37614_chunk mpe_15 (cost=1.67..232847.68 rows=13418589 width=51)
-> Parallel Seq Scan on compress_hyper_24_40145_chunk (cost=0.00..98661.79 rows=139779 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37610_chunk mpe_13 (cost=1.67..232805.49 rows=13417174 width=51)
-> Parallel Seq Scan on compress_hyper_24_40140_chunk (cost=0.00..98633.74 rows=139774 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37650_chunk mpe_31 (cost=1.67..232456.05 rows=13400596 width=51)
-> Parallel Seq Scan on compress_hyper_24_40158_chunk (cost=0.00..98450.09 rows=139609 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37598_chunk mpe_7 (cost=1.66..232596.28 rows=13413305 width=52)
-> Parallel Seq Scan on compress_hyper_24_40120_chunk (cost=0.00..98463.22 rows=139722 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37587_chunk mpe_2 (cost=1.66..232462.26 rows=13404283 width=52)
-> Parallel Seq Scan on compress_hyper_24_40115_chunk (cost=0.00..98419.43 rows=139643 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37600_chunk mpe_8 (cost=1.66..232584.99 rows=13413178 width=52)
-> Parallel Seq Scan on compress_hyper_24_40138_chunk (cost=0.00..98453.22 rows=139722 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37607_chunk mpe_11 (cost=1.66..232598.48 rows=13415591 width=52)
-> Parallel Seq Scan on compress_hyper_24_40139_chunk (cost=0.00..98442.57 rows=139757 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37602_chunk mpe_9 (cost=1.66..232556.74 rows=13413234 width=52)
-> Parallel Seq Scan on compress_hyper_24_40137_chunk (cost=0.00..98424.39 rows=139739 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37616_chunk mpe_16 (cost=1.66..232616.99 rows=13418222 width=51)
-> Parallel Seq Scan on compress_hyper_24_40146_chunk (cost=0.00..98434.77 rows=139777 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37604_chunk mpe_10 (cost=1.66..232540.57 rows=13414911 width=52)
-> Parallel Seq Scan on compress_hyper_24_40136_chunk (cost=0.00..98391.46 rows=139746 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37621_chunk mpe_18 (cost=1.66..232569.31 rows=13418356 width=51)
-> Parallel Seq Scan on compress_hyper_24_40142_chunk (cost=0.00..98385.75 rows=139775 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37624_chunk mpe_20 (cost=1.66..232540.63 rows=13418288 width=51)
-> Parallel Seq Scan on compress_hyper_24_40141_chunk (cost=0.00..98357.75 rows=139775 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37623_chunk mpe_19 (cost=1.66..232514.65 rows=13418390 width=51)
-> Parallel Seq Scan on compress_hyper_24_40143_chunk (cost=0.00..98330.75 rows=139775 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37626_chunk mpe_21 (cost=1.66..232499.74 rows=13418199 width=51)
-> Parallel Seq Scan on compress_hyper_24_40148_chunk (cost=0.00..98317.76 rows=139776 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37638_chunk mpe_26 (cost=1.66..232483.61 rows=13418065 width=51)
-> Parallel Seq Scan on compress_hyper_24_40153_chunk (cost=0.00..98302.96 rows=139796 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37646_chunk mpe_29 (cost=1.66..232073.35 rows=13398366 width=51)
-> Parallel Seq Scan on compress_hyper_24_40156_chunk (cost=0.00..98089.69 rows=139569 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37635_chunk mpe_25 (cost=1.66..232364.39 rows=13418256 width=51)
-> Parallel Seq Scan on compress_hyper_24_40152_chunk (cost=0.00..98181.83 rows=139783 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37639_chunk mpe_27 (cost=1.66..232134.12 rows=13403967 width=51)
-> Parallel Seq Scan on compress_hyper_24_40154_chunk (cost=0.00..98094.45 rows=139645 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37633_chunk mpe_24 (cost=1.66..232298.33 rows=13416661 width=51)
-> Parallel Seq Scan on compress_hyper_24_40151_chunk (cost=0.00..98131.72 rows=139772 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37641_chunk mpe_28 (cost=1.66..231926.76 rows=13398209 width=51)
-> Parallel Seq Scan on compress_hyper_24_40155_chunk (cost=0.00..97944.67 rows=139567 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37628_chunk mpe_22 (cost=1.66..232131.88 rows=13415438 width=51)
-> Parallel Seq Scan on compress_hyper_24_40149_chunk (cost=0.00..97977.49 rows=139749 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37631_chunk mpe_23 (cost=1.66..232128.79 rows=13415818 width=51)
-> Parallel Seq Scan on compress_hyper_24_40150_chunk (cost=0.00..97970.61 rows=139761 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37648_chunk mpe_30 (cost=1.66..231827.52 rows=13398763 width=51)
-> Parallel Seq Scan on compress_hyper_24_40157_chunk (cost=0.00..97839.89 rows=139589 width=350)
-> Custom Scan (ColumnarScan) on _hyper_21_37585_chunk mpe_1 (cost=1.55..217025.67 rows=13040918 width=52)
Vectorized Filter: ((casovna_znacka > '2025-05-01 00:00:00+02'::timestamp with time zone) AND (casovna_znacka <= '2025-06-01 00:00:00+02'::timestamp with time zone))
-> Parallel Seq Scan on compress_hyper_24_40114_chunk (cost=0.00..86616.49 rows=139627 width=350)
Filter: ((_ts_meta_max_1 > '2025-05-01 00:00:00+02'::timestamp with time zone) AND (_ts_meta_min_1 <= '2025-06-01 00:00:00+02'::timestamp with time zone))
-> Hash (cost=7.11..7.11 rows=7 width=67)
-> Nested Loop (cost=0.70..7.11 rows=7 width=67)
-> Nested Loop (cost=0.42..5.67 rows=4 width=41)
-> Index Scan using t_sumarna_mm_formula_mm_idx2 on t_sumarna_mm_formula_mm smfc (cost=0.14..2.36 rows=1 width=29)
Index Cond: ((dis = 2) AND ((enotni_ident_mm)::text = '2-7156'::text))
-> Index Scan using t_sumarna_mm_formula_idx2 on t_sumarna_mm_formula smf (cost=0.28..3.26 rows=4 width=20)
Index Cond: (id_sumarna_mm_formula_mm = smfc.id_sumarna_mm_formula_mm)
-> Index Scan using t_sumarna_mm_clen_idx1 on t_sumarna_mm_clen smc (cost=0.28..0.34 rows=2 width=26)
Index Cond: (id_sumarna_mm_clen_pravila = smf.id_sumarna_mm_clen_pravila)
Planning:
Buffers: shared hit=15306 read=15
I/O Timings: shared read=7.531Comparing those two explains I see Index Scan on chunks on first explain and expected rows to be returned for individual chunk is 1. On second plan there is Sequential Scan and expected rows to be returned is 13 million per chunk.
- Now I trimmed down above production query to only access hyper-table and executed it on test database:
explain (buffers)
select
*
from
prod.t_merilni_podatki_energija_15_min vmpems '<--- this is hyper-table
where
dis = 3 and
vmpems.enotni_ident_mm = '3-13725' and
vmpems.casovna_znacka >'2025-01-01' and
vmpems.casovna_znacka <= '2025-02-01';and query is executed in 5 milliseconds. See: trimmed_query_to_hypertable_only.txt
Compression hypertable is not an issue by itself.
- In the "merged query" if I changed time WHERE condition from year 2025 to 2026 on my test database:
WHERE
dis = 2 and
enotni_ident_mm = '2-7156' and
casovna_znacka >'2026-05-01' and --> change from 2025 to 2026
casovna_znacka <= '2026-06-01' --> change from 2025 to 2026to target the non-compressed chunks only, then query execution jumps back to expected range of 100 ms and "explain (analyze, buffers) query...". See: non_compressed_chunks_query_explain.txt
So the same "merged query" that is executed against all non-compressed chunks executes in reasonable time in range of 100 ms.
CONCLUSION: If "merged query" is executed against compressed chunks on TimescaleDB 2.20.1 it takes 68 miliseconds, but the same query on the same data on TimescaleDB 2.29.2 it takes more then 15 min and then fails (because of statement_timeout parameter is set).
Source: timescale/timescaledb