kgyrtkirk
commented
1 year ago I think this issue doesn't affect the correctness of the resultset - so this is not Bug; its more like a feature request for a missed optimization opportunity; to detect and optionally rewrite conditions like:
t > timezone(col,const)` => `t > timezone(col,const) and t > const-24ha bit more self contained repro; (checked with 2.9.1)
drop table if exists conditions;
drop table if exists opaque_timezone;
CREATE TABLE conditions (
t timestamp with time ZONE NOT NULL,
temperature NUMERIC
);
SELECT create_hypertable('conditions', 't', chunk_time_interval => INTERVAL '1 days');
INSERT INTO conditions (t, temperature)
SELECT
generate_series('2022-01-01 00:00:00-00'::timestamptz, '2022-01-31 23:59:59-00'::timestamptz, '1 hour'),
0.25;
create table opaque_timezone as select 'Europe/Budapest' as tz;
explain analyze
SELECT *
FROM conditions
join opaque_timezone on (true)
WHERE t between timezone(tz , '2022-01-10 00:00:00+0000'::timestamptz) and timezone(tz,'2022-01-13 23:59:59+0000'::timestamptz)
;
explain analyze
SELECT *
FROM conditions
join opaque_timezone on (true)
WHERE t between timezone(tz , '2022-01-10 00:00:00+0000'::timestamptz) and timezone(tz,'2022-01-13 23:59:59+0000'::timestamptz)
and t between '2022-01-10 00:00:00+0000'::timestamptz - INTERVAL '1' day and '2022-01-10 00:00:00+0000'::timestamptz + INTERVAL '1' day
;Thank you for reporting this - we will get back to it!
andnig
What type of bug is this?
Performance issue
What subsystems and features are affected?
Query executor, Query planner
What happened?
We are experiencing bad performance when the time bounds of a query are not passe in as constants. This happens frequently, for example when we need to query daily aggregates in the timezone of a particular location. As a workaround, we can add additional constraints to query data within +/- 1 day of the specified time interval. It would be great if this could be automated though, as it should be possible to deduct these additional time exclusions from the timezone statements. The improved query runs 15 times faster
An example is illustrated below with queries and resulting plans
Good plan: This query adds additional time bounds with +/- 24 hours to have constant time bounds that will include the needed time interval guaranteed. Adding these redundant constraints improves performance significantly
EXPLAIN (ANALYSE, FORMAT JSON) SELECT * FROM node_measurements join positions p ON node_measurements.position_id = p.position_id join locations l on p.location_id = l.location_id -- due to the timezone conversion that depends on reading the timezone from the DB the timestamps can not be computed or known before execution. WHERE time between timezone(l.timezone , '2023-01-02 00:00:00+0000'::timestamptz) and timezone(l.timezone,'2023-01-03 23:59:59+0000'::timestamptz) -- The below constraint is not needed but improves performance significantly as it allows the planner to exclude chunks at planning time due to these timestamps being constants AND time between '2023-01-01 00:00:00+0000'::timestamptz and '2023-01-04 23:59:59+0000'::timestamptz AND location_name = 'Max Home' AND measurement_type = 'temperature_set' AND src_mac_id = 'FFFF14B4577FE5F0' ORDER by time DESC limit 100;
Bad plan:
Without the extra constant time bounds in the query, the resulting plan is looking at every single chunk in the table instead of excluding them according to the time bounds of the query. The query runs significantly slower because of this.
EXPLAIN (ANALYSE, FORMAT JSON) SELECT * FROM node_measurements join positions p ON node_measurements.position_id = p.position_id join locations l on p.location_id = l.location_id WHERE time between timezone(l.timezone , '2023-01-02 00:00:00+0000'::timestamptz) and timezone(l.timezone,'2023-01-03 23:59:59+0000'::timestamptz) AND location_name = 'Max Home' AND measurement_type = 'temperature_set' AND src_mac_id = 'FFFF14B4577FE5F0' ORDER by time DESC limit 100;
I did report this before here: https://github.com/timescale/timescaledb/issues/2670 but opening it again since it has been a while and is on a completely new version now. feel free
TimescaleDB version affected
2.8.1 and below, likely latest also
PostgreSQL version used
13.9
What operating system did you use?
whatever timescale powered by aiven is running on
What installation method did you use?
Other
What platform did you run on?
Amazon Web Services (AWS)
Relevant log output and stack trace
How can we reproduce the bug?