gugu
commented
1 month ago If I try to trick optimizer it works, but does not use index in compressed rows
> explain select count(*) from clicks where host='REDACTED' || substring(now()::text, 1, 0) and dt > now() - interval '10 days';
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Finalize Aggregate (cost=378713.81..378713.82 rows=1 width=8) │
│ -> Gather (cost=1000.59..378713.75 rows=24 width=8) │
│ Workers Planned: 2 │
│ -> Parallel Custom Scan (ChunkAppend) on clicks (cost=0.58..377711.35 rows=12 width=8) │
│ Chunks excluded during startup: 0 │
│ -> Partial Aggregate (cost=732.70..732.71 rows=1 width=8) │
│ -> Parallel Index Only Scan using _hyper_109_4222_chunk_clicks_host_dt_idx on _hyper_109_4222_chunk (cost=0.58..725.09 rows=3044 width=0) │
│ Index Cond: ((host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) AND (dt > (now() - '10 days'::interval)) AND (dt > '2024-05-20 09:56:51.80338+00'::timestamp with time zone)) │
│ -> Partial Aggregate (cost=407.08..407.09 rows=1 width=8) │
│ -> Parallel Index Only Scan using _hyper_109_4223_chunk_clicks_host_dt_idx on _hyper_109_4223_chunk (cost=0.58..400.09 rows=2794 width=0) │
│ Index Cond: ((host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) AND (dt > (now() - '10 days'::interval)) AND (dt > '2024-05-20 09:56:51.80338+00'::timestamp with time zone)) │
│ -> Partial Aggregate (cost=1006.61..1006.62 rows=1 width=8) │
│ -> Parallel Index Only Scan using _hyper_109_4221_chunk_clicks_host_dt_idx on _hyper_109_4221_chunk (cost=0.58..999.88 rows=2692 width=0) │
│ Index Cond: ((host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) AND (dt > (now() - '10 days'::interval)) AND (dt > '2024-05-20 09:56:51.80338+00'::timestamp with time zone)) │
│ -> Partial Aggregate (cost=298.59..298.60 rows=1 width=8) │
│ -> Parallel Index Only Scan using _hyper_109_4228_chunk_clicks_host_dt_idx on _hyper_109_4228_chunk (cost=0.58..294.92 rows=1470 width=0) │
│ Index Cond: ((host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) AND (dt > (now() - '10 days'::interval)) AND (dt > '2024-05-20 09:56:51.80338+00'::timestamp with time zone)) │
│ -> Partial Aggregate (cost=270.00..270.01 rows=1 width=8) │
│ -> Parallel Index Only Scan using _hyper_109_4227_chunk_clicks_host_dt_idx on _hyper_109_4227_chunk (cost=0.58..266.36 rows=1458 width=0) │
│ Index Cond: ((host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) AND (dt > (now() - '10 days'::interval)) AND (dt > '2024-05-20 09:56:51.80338+00'::timestamp with time zone)) │
│ -> Partial Aggregate (cost=122.08..122.09 rows=1 width=8) │
│ -> Parallel Index Only Scan using _hyper_109_4229_chunk_clicks_host_dt_idx on _hyper_109_4229_chunk (cost=0.58..118.54 rows=1419 width=0) │
│ Index Cond: ((host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) AND (dt > (now() - '10 days'::interval)) AND (dt > '2024-05-20 09:56:51.80338+00'::timestamp with time zone)) │
│ -> Partial Aggregate (cost=166.91..166.92 rows=1 width=8) │
│ -> Parallel Index Only Scan using _hyper_109_4250_chunk_clicks_host_dt_idx on _hyper_109_4250_chunk (cost=0.58..165.26 rows=659 width=0) │
│ Index Cond: ((host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) AND (dt > (now() - '10 days'::interval)) AND (dt > '2024-05-20 09:56:51.80338+00'::timestamp with time zone)) │
│ -> Partial Aggregate (cost=104.95..104.96 rows=1 width=8) │
│ -> Parallel Index Only Scan using _hyper_109_4249_chunk_clicks_host_dt_idx on _hyper_109_4249_chunk (cost=0.58..103.07 rows=753 width=0) │
│ Index Cond: ((host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) AND (dt > (now() - '10 days'::interval)) AND (dt > '2024-05-20 09:56:51.80338+00'::timestamp with time zone)) │
│ -> Partial Aggregate (cost=35.35..35.36 rows=1 width=8) │
│ -> Parallel Index Only Scan using _hyper_109_4248_chunk_clicks_host_dt_idx on _hyper_109_4248_chunk (cost=0.58..33.67 rows=672 width=0) │
│ Index Cond: ((host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) AND (dt > (now() - '10 days'::interval)) AND (dt > '2024-05-20 09:56:51.80338+00'::timestamp with time zone)) │
│ -> Partial Aggregate (cost=111799.42..111799.43 rows=1 width=8) │
│ -> Custom Scan (DecompressChunk) on _hyper_109_4181_chunk (cost=0.21..8694.42 rows=41242000 width=0) │
│ Filter: (host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) │
│ Vectorized Filter: (dt > (now() - '10 days'::interval)) │
│ -> Parallel Seq Scan on compress_hyper_110_4224_chunk (cost=0.00..8694.42 rows=41242 width=68) │
│ -> Partial Aggregate (cost=131155.58..131155.59 rows=1 width=8) │
│ -> Custom Scan (DecompressChunk) on _hyper_109_4183_chunk (cost=0.20..9760.58 rows=48558000 width=0) │
│ Filter: (host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) │
│ Vectorized Filter: (dt > (now() - '10 days'::interval)) │
│ -> Parallel Seq Scan on compress_hyper_110_4226_chunk (cost=0.00..9760.58 rows=48558 width=68) │
│ -> Partial Aggregate (cost=131611.95..131611.96 rows=1 width=8) │
│ -> Custom Scan (DecompressChunk) on _hyper_109_4182_chunk (cost=0.20..9749.45 rows=48745000 width=0) │
│ Filter: (host = ('REDACTED'::text || "substring"((now())::text, 1, 0))) │
│ Vectorized Filter: (dt > (now() - '10 days'::interval)) │
│ -> Parallel Seq Scan on compress_hyper_110_4225_chunk (cost=0.00..9749.45 rows=48745 width=67) │
│ JIT: │
│ Functions: 50 │
│ Options: Inlining false, Optimization false, Expressions true, Deforming true │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
antekresic
What type of bug is this?
Incorrect result
What subsystems and features are affected?
Query planner
What happened?
After upgrade timescaledb 2.14 => 2.15.1 it recreated chunks for partitioned table and does not use old chunks if I run a query with filter by partitioned column.
Example:
Table structure:
List of latest chunks:
Dimensions:
TimescaleDB version affected
2.15.1
PostgreSQL version used
14.12
What operating system did you use?
(Ubuntu 14.12-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
What installation method did you use?
Docker
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace
How can we reproduce the bug?