Timescaledb continuous aggregate materialized only = true is about 100 times faster than materialized only = false

[sarv_sarv]

look at this one:

query:explain analyze select * from history_data_crypto_1m where slug=‘cardano’ and time_1m > ‘2023-02-01’ and time_1m< ‘2023-11-02’ order by time_1m desc limit 100;

matrilized only true

 Limit  (cost=0.56..101.05 rows=100 width=58) (actual time=0.134..1.442 rows=100 loops=1)
   ->  Custom Scan (ChunkAppend) on _materialized_hypertable_103 srt  (cost=0.56..82273.33 rows=81871 width=58) (actual time=0.131..1.418 rows=100 loops=1)
         Order: srt.time_1m DESC
         ->  Index Scan Backward using _hyper_103_2229_chunk_crypto_slug_time_1m_idx on _hyper_103_2229_chunk srt_1  (cost=0.56..82273.33 rows=81871 width=58) (actual time=0.129..1.389 rows=100 loops=1)
               Index Cond: ((slug = 'cardano'::text) AND (time_1m > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (time_1m < '2023-11-02 00:00:00+00'::timestamp with time zone))
         ->  Custom Scan (DecompressChunk) on _hyper_103_2208_chunk srt_2  (cost=0.51..50.62 rows=100000 width=58) (never executed)
               Filter: ((time_1m > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (time_1m < '2023-11-02 00:00:00+00'::timestamp with time zone))
               ->  Index Scan Backward using compress_hyper_108_2558_chunk__compressed_hypertable_108_slug__ on compress_hyper_108_2558_chunk  (cost=0.29..50.62 rows=100 width=225) (never executed)
                     Index Cond: (slug = 'cardano'::text)
                     Filter: ((_ts_meta_max_1 > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (_ts_meta_min_1 < '2023-11-02 00:00:00+00'::timestamp with time zone))
         ->  Custom Scan (DecompressChunk) on _hyper_103_2207_chunk srt_3  (cost=25.56..51.12 rows=2000 width=58) (never executed)
               Filter: ((time_1m > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (time_1m < '2023-11-02 00:00:00+00'::timestamp with time zone))
               ->  Index Scan Backward using compress_hyper_108_2401_chunk__compressed_hypertable_108_slug__ on compress_hyper_108_2401_chunk  (cost=0.29..51.12 rows=2 width=225) (never executed)
                     Index Cond: (slug = 'cardano'::text)
                     Filter: ((_ts_meta_max_1 > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (_ts_meta_min_1 < '2023-11-02 00:00:00+00'::timestamp with time zone))
 Planning Time: 7.185 ms
 Execution Time: 1.707 ms

matrilized only false

 Limit  (cost=88777.99..88778.24 rows=100 width=58) (actual time=901.865..901.888 rows=100 loops=1)
   ->  Sort  (cost=88777.99..89237.69 rows=183881 width=58) (actual time=901.863..901.877 rows=100 loops=1)
         Sort Key: srt.time_1m DESC
         Sort Method: top-N heapsort  Memory: 53kB
         ->  Append  (cost=25.56..81750.19 rows=183881 width=58) (actual time=0.566..817.439 rows=187862 loops=1)
               ->  Custom Scan (ChunkAppend) on _materialized_hypertable_103 srt  (cost=25.56..78975.75 rows=183875 width=58) (actual time=0.565..797.310 rows=187861 loops=1)
                     Chunks excluded during startup: 0
                     ->  Custom Scan (DecompressChunk) on _hyper_103_2207_chunk srt_1  (cost=25.56..51.12 rows=2000 width=58) (actual time=0.564..1.312 rows=1439 loops=1)
                           Filter: ((time_1m > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (time_1m < '2023-11-02 00:00:00+00'::timestamp with time zone) AND (time_1m < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(103)), '-infinity'::timestamp with time zone)))
                           Rows Removed by Filter: 361
                           ->  Index Scan using compress_hyper_108_2401_chunk__compressed_hypertable_108_slug__ on compress_hyper_108_2401_chunk  (cost=0.29..51.12 rows=2 width=221) (actual time=0.124..0.130 rows=2 loops=1)
                                 Index Cond: (slug = 'cardano'::text)
                                 Filter: ((_ts_meta_max_1 > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (_ts_meta_min_1 < '2023-11-02 00:00:00+00'::timestamp with time zone))
                                 Rows Removed by Filter: 99
                     ->  Custom Scan (DecompressChunk) on _hyper_103_2208_chunk srt_2  (cost=0.51..50.62 rows=100000 width=58) (actual time=0.353..54.405 rows=100715 loops=1)
                           Filter: ((time_1m > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (time_1m < '2023-11-02 00:00:00+00'::timestamp with time zone) AND (time_1m < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(103)), '-infinity'::timestamp with time zone)))
                           ->  Index Scan using compress_hyper_108_2558_chunk__compressed_hypertable_108_slug__ on compress_hyper_108_2558_chunk  (cost=0.29..50.62 rows=100 width=221) (actual time=0.056..0.380 rows=101 loops=1)
                                 Index Cond: (slug = 'cardano'::text)
                                 Filter: ((_ts_meta_max_1 > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (_ts_meta_min_1 < '2023-11-02 00:00:00+00'::timestamp with time zone))
                     ->  Bitmap Heap Scan on _hyper_103_2229_chunk srt_3  (cost=943.09..78874.01 rows=81875 width=58) (actual time=51.909..715.434 rows=85707 loops=1)
                           Recheck Cond: (slug = 'cardano'::text)
                           Filter: ((time_1m > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (time_1m < '2023-11-02 00:00:00+00'::timestamp with time zone) AND (time_1m < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(103)), '-infinity'::timestamp with time zone)))
                           Heap Blocks: exact=85381
                           ->  Bitmap Index Scan on _hyper_103_2229_chunk_crypto_slug_1m_idx  (cost=0.00..922.62 rows=81875 width=0) (actual time=30.187..30.187 rows=85816 loops=1)
                                 Index Cond: (slug = 'cardano'::text)
               ->  HashAggregate  (cost=16.12..16.23 rows=6 width=58) (actual time=0.284..0.285 rows=1 loops=1)
                     Group Key: time_bucket('00:01:00'::interval, srt_4."time"), srt_4.slug
                     ->  Custom Scan (ChunkAppend) on history_data_crypto_1s srt_4  (cost=0.43..16.02 rows=6 width=66) (actual time=0.089..0.223 rows=8 loops=1)
                           Chunks excluded during startup: 5
                           ->  Index Scan using _hyper_19_15927_chunk_idx_unique_slug_time on _hyper_19_15927_chunk srt_5  (cost=0.43..2.67 rows=1 width=58) (actual time=0.086..0.215 rows=8 loops=1)
                                 Index Cond: ((slug = 'cardano'::text) AND ("time" >= COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(103)), '-infinity'::timestamp with time zone)) AND ("time" > '2023-02-01 00:00:00+00'::timestamp with time zone) AND ("time" < '2023-11-02 00:01:00+00'::timestamp with time zone))
                                 Filter: ((time_bucket('00:01:00'::interval, "time") > '2023-02-01 00:00:00+00'::timestamp with time zone) AND (time_bucket('00:01:00'::interval, "time") < '2023-11-02 00:00:00+00'::timestamp with time zone))
 Planning Time: 51.654 ms
 Execution Time: 902.408 ms

also this question

Materialized_only=true vs materialized_only=false. query speed drop too much

hi. i have a continuous aggregate when i set materialized_only=true query speed is good but otherwise (materialized_only=false) query speed is too slow. i have a refresh policy which run every five minute and refresh continuous aggregate. also in this 5 minute there is very less data. or may be no data. why speed is too much lower? when refresh policy is 5 minute then i think older than 5 minute should be read from materialized view itself and speed should not be that much slower