The now() function in the where clause is slow for me, I found the article on how Timescale fixed this in version 2.7, but after updating from 2.50 to 2.8.1 it is still slow and is searching all chunks. Fixed dates are very very fast. How can I figure out why this isn’t working?
Can you post the output of EXPLAIN:
EXPLAIN (ANALYZE on, BUFFERS on) ~YOUR QUERY HERE~;
Also, just to double check your version, what’s the output of \dx ?
Here is the explain, also I checked and my version is 2.8.1. Output of pg_extension table below. When I run with a static date it only shows 1 chunk in the explain output. This is exactly what I expected from V2.5. If you want the full 9000 lines of output let me know.
24603 timescaledb 10 2200 f 2.8.1
Buffers: shared hit=5610 read=5155 dirtied=45
HashAggregate (cost=64795.40…65125.69 rows=26423 width=28) (actual time=1452.656…1453.658 rows=2 loops=1)
" Group Key: _hyper_413_2755_chunk.meter_number, (time_bucket(‘1 day’::interval, _hyper_413_2755_chunk.interval_end))"
Buffers: shared hit=5610 read=5155 dirtied=45
I/O Timings: read=1306.666
→ Append (cost=1.06…57920.82 rows=916611 width=28) (actual time=1446.195…1453.356 rows=529 loops=1)
I/O Timings: read=1306.666
→ Custom Scan (DecompressChunk) on _hyper_413_2755_chunk (cost=1.06…3.56 rows=1000 width=28) (actual time=0.036…0.038 rows=0 loops=1)
" Filter: (time_bucket(‘1 day’::interval, interval_end) >= ((now())::date - ‘2 days’::interval))"
Buffers: shared hit=1
→ Seq Scan on compress_hyper_419_3017_chunk (cost=0.00…1.06 rows=1 width=106) (actual time=0.035…0.035 rows=0 loops=1)
" Filter: ((unit = ‘Temperature Degrees C (Max)’::text) AND (meter_number = ‘1ND090755992’::text))"
Rows Removed by Filter: 4
Buffers: shared hit=1
→ Custom Scan (DecompressChunk) on _hyper_413_2756_chunk (cost=1.06…3.56 rows=1000 width=28) (actual time=0.013…0.014 rows=0 loops=1)
" Filter: (time_bucket(‘1 day’::interval, interval_end) >= ((now())::date - ‘2 days’::interval))"
Buffers: shared hit=1
→ Seq Scan on compress_hyper_419_3018_chunk (cost=0.00…1.06 rows=1 width=106) (actual time=0.012…0.013 rows=0 loops=1)
" Filter: ((unit = ‘Temperature Degrees C (Max)’::text) AND (meter_number = ‘1ND090755992’::text))"
Rows Removed by Filter: 4
Buffers: shared hit=1
→ Custom Scan (DecompressChunk) on _hyper_413_2757_chunk (cost=1.06…3.56 rows=1000 width=28) (actual time=0.009…0.010 rows=0 loops=1)
" Filter: (time_bucket(‘1 day’::interval, interval_end) >= ((now())::date - ‘2 days’::interval))"
Buffers: shared hit=1
→ Seq Scan on compress_hyper_419_3019_chunk (cost=0.00…1.06 rows=1 width=106) (actual time=0.009…0.009 rows=0 loops=1)
" Filter: ((unit = ‘Temperature Degrees C (Max)’::text) AND (meter_number = ‘1ND090755992’::text))"
Rows Removed by Filter: 4
Buffers: shared hit=1
…AROUND 1000 more chunks
→ Index Scan using _hyper_413_9361_chunk_gridwide_power_interval_meter_number_idx on _hyper_413_9361_chunk (cost=0.57…2717.03 rows=95 width=28) (actual time=23.824…23.824 rows=0 loops=1)
" Index Cond: (meter_number = ‘1ND090755992’::text)"
" Filter: ((unit = ‘Temperature Degrees C (Max)’::text) AND (time_bucket(‘1 day’::interval, interval_end) >= ((now())::date - ‘2 days’::interval)))"
Rows Removed by Filter: 2304
Buffers: shared hit=1 read=72 dirtied=23
I/O Timings: read=22.481
→ Index Scan using _hyper_413_9368_chunk_gridwide_power_interval_meter_number_idx on _hyper_413_9368_chunk (cost=0.57…2859.90 rows=101 width=28) (actual time=0.930…4.347 rows=312 loops=1)
" Index Cond: (meter_number = ‘1ND090755992’::text)"
" Filter: ((unit = ‘Temperature Degrees C (Max)’::text) AND (time_bucket(‘1 day’::interval, interval_end) >= ((now())::date - ‘2 days’::interval)))"
Rows Removed by Filter: 2184
Buffers: shared hit=66 read=11
I/O Timings: read=3.380
→ Index Scan using _hyper_413_9378_chunk_gridwide_power_interval_meter_number_idx on _hyper_413_9378_chunk (cost=0.56…2044.67 rows=71 width=28) (actual time=0.346…2.701 rows=217 loops=1)
" Index Cond: (meter_number = ‘1ND090755992’::text)"
" Filter: ((unit = ‘Temperature Degrees C (Max)’::text) AND (time_bucket(‘1 day’::interval, interval_end) >= ((now())::date - ‘2 days’::interval)))"
Rows Removed by Filter: 1519
Buffers: shared hit=48 read=9 dirtied=22
I/O Timings: read=1.982
Planning Time: 655.806 ms
Execution Time: 1479.282 ms
And here is the actual query. I’ve tried without casting now to date, using normal fields instead of time buckets, no change on any of those approaches. But happy to try any of them again in case I missed something.
explain (analyze on, buffers on)
select meter_number,
time_bucket(‘1 day’, interval_end) as day_bucket,
max(value) as max_temp_today
from power_interval
where time_bucket(‘1 day’, interval_end) >= now()::date - interval ‘2 days’
– and time_bucket(‘1 day’, interval_end) >= ‘2022-10-25’
and unit = ‘Temperature Degrees C (Max)’
and meter_number = ‘XXXXXXXXXXX’
group by meter_number, day_bucket
;
The now() optimization applies to expressions like column > now() - interval but it will not apply when the column is wrapped in a time_bucket call. Is that from a cagg query? The use of time_bucket like in your query in the WHERE clause will prevent the now() optimization from activating.
I adjusted the query to use a column instead of a bucket and it has the same behavior unfortunately and is even slower.
explain analyze
select meter_number,
time_bucket('1 day', interval_end) as day_bucket,
max(value) as max_temp_today
from power_interval
where unit = 'Temperature Degrees C (Max)'
-- and time_bucket('1 day', interval_end) >= now()::date - interval '1 days'
-- and time_bucket('1 day', interval_end) >= '2022-10-27'
and interval_end >= now() - interval '1 days'
and meter_number = '1ND090079049'
group by meter_number, day_bucket
;
Rows Removed by Filter: 4
HashAggregate (cost=21296.27..21310.01 rows=1099 width=28) (actual time=147495.216..147496.324 rows=2 loops=1)
" Group Key: _hyper_413_2755_chunk.meter_number, (time_bucket('1 day'::interval, _hyper_413_2755_chunk.interval_end))"
-> Append (cost=1.06..14387.40 rows=921183 width=28) (actual time=147494.177..147496.229 rows=194 loops=1)
-> Custom Scan (DecompressChunk) on _hyper_413_2755_chunk (cost=1.06..3.56 rows=1000 width=28) (actual time=0.013..0.015 rows=0 loops=1)
" Filter: (interval_end >= (now() - '1 day'::interval))"
-> Seq Scan on compress_hyper_419_3017_chunk (cost=0.00..1.06 rows=1 width=106) (actual time=0.013..0.013 rows=0 loops=1)
" Filter: ((unit = 'Temperature Degrees C (Max)'::text) AND (meter_number = '1ND090079049'::text))"
Rows Removed by Filter: 4
-> Custom Scan (DecompressChunk) on _hyper_413_2756_chunk (cost=1.06..3.56 rows=1000 width=28) (actual time=0.022..0.024 rows=0 loops=1)
" Filter: (interval_end >= (now() - '1 day'::interval))"
-> Seq Scan on compress_hyper_419_3018_chunk (cost=0.00..1.06 rows=1 width=106) (actual time=0.022..0.022 rows=0 loops=1)
" Filter: ((unit = 'Temperature Degrees C (Max)'::text) AND (meter_number = '1ND090079049'::text))"
Rows Removed by Filter: 4
-> Custom Scan (DecompressChunk) on _hyper_413_2757_chunk (cost=1.06..3.56 rows=1000 width=28) (actual time=0.009..0.011 rows=0 loops=1)
" Filter: (interval_end >= (now() - '1 day'::interval))"
-> Seq Scan on compress_hyper_419_3019_chunk (cost=0.00..1.06 rows=1 width=106) (actual time=0.008..0.009 rows=0 loops=1)
" Filter: ((unit = 'Temperature Degrees C (Max)'::text) AND (meter_number = '1ND090079049'::text))"
Rows Removed by Filter: 4
-> Custom Scan (DecompressChunk) on _hyper_413_2758_chunk (cost=1.06..3.56 rows=1000 width=28) (actual time=0.009..0.010 rows=0 loops=1)
" Filter: (interval_end >= (now() - '1 day'::interval))"
-> Seq Scan on compress_hyper_419_3020_chunk (cost=0.00..1.06 rows=1 width=106) (actual time=0.008..0.008 rows=0 loops=1)
" Filter: ((unit = 'Temperature Degrees C (Max)'::text) AND (meter_number = '1ND090079049'::text))"
Rows Removed by Filter: 4
-> Custom Scan (DecompressChunk) on _hyper_413_2759_chunk (cost=1.06..3.56 rows=1000 width=28) (actual time=0.008..0.010 rows=0 loops=1)
" Filter: (interval_end >= (now() - '1 day'::interval))"
-> Seq Scan on compress_hyper_419_3021_chunk (cost=0.00..1.06 rows=1 width=106) (actual time=0.008..0.008 rows=0 loops=1)
" Filter: ((unit = 'Temperature Degrees C (Max)'::text) AND (meter_number = '1ND090079049'::text))"
Rows Removed by Filter: 4
-> Custom Scan (DecompressChunk) on _hyper_413_2760_chunk (cost=1.06..3.56 rows=1000 width=28) (actual time=0.008..0.009 rows=0 loops=1)
" Filter: (interval_end >= (now() - '1 day'::interval))"
-> Seq Scan on compress_hyper_419_3022_chunk (cost=0.00..1.06 rows=1 width=106) (actual time=0.008..0.008 rows=0 loops=1)
" Filter: ((unit = 'Temperature Degrees C (Max)'::text) AND (meter_number = '1ND090079049'::text))"
Rows Removed by Filter: 4
-> Custom Scan (DecompressChunk) on _hyper_413_2761_chunk (cost=1.06..3.56 rows=1000 width=28) (actual time=0.013..0.014 rows=0 loops=1)
" Filter: (interval_end >= (now() - '1 day'::interval))"
-> Seq Scan on compress_hyper_419_3023_chunk (cost=0.00..1.06 rows=1 width=106) (actual time=0.012..0.013 rows=0 loops=1)
" Filter: ((unit = 'Temperature Degrees C (Max)'::text) AND (meter_number = '1ND090079049'::text))"
Rows Removed by Filter: 4
-> Custom Scan (DecompressChunk) on _hyper_413_2762_chunk (cost=1.06..3.56 rows=1000 width=28) (actual time=0.009..0.010 rows=0 loops=1)
" Filter: (interval_end >= (now() - '1 day'::interval))"
-> Seq Scan on compress_hyper_419_3024_chunk (cost=0.00..1.06 rows=1 width=106) (actual time=0.008..0.008 rows=0 loops=1)
" Filter: ((unit = 'Temperature Degrees C (Max)'::text) AND (meter_number = '1ND090079049'::text))"
.....all chunks, etc.
I still can’t get this to work, does the now optimization only work on dimensions defined after the update? Is there something else I need to do in order to make this work?