Slow lateral join queries, doesn't filter chunk

TimescaleDB and PostgreSQL
1

Hi,
It seems the queries are slower when executed in a lateral join, here with a single element loop , than as a plain query and looking at explain it seems it doesn’t exclude the chunks like it does with the plain query. Is this a known behavior ? Maybe related to the fact that yearts is only known at runtime ? Is there a way around that ?

lateral join query :

select time, code_id, value from
(SELECT
    (step||'-01-01')::timestamptz AS yearts
    FROM unnest(ARRAY['2023']) g(step)) g_offsets
  JOIN LATERAL (
	  select time, code_id, value
	  from total
	  where time between yearts::timestamptz and yearts::timestamptz + '1 year'::interval
	   order by 1) l on true

plain query :

select time, code_id, value from
	  from total
	  where time between '2023-01-01T00:00:00Z' and '2023-01-01T00:00:00Z'::timestamptz + '1 year'::interval
	   order by 1

The idea behind the lateral join is that the ARRAY[‘2023’] is customisable with other years, and also the actual query is more complex with several level of grouping and time offset, but the idea and slowdown remains with a failure to filter chunks. So when there are many years, it will loop multiple times and fail to exclude the chunks out of the time range multiple times.

Thanks.

2

Hi @hko, have you tried to use time_bucket function instead?

Something like:

select time_bucket('1 year', time), array_agg(code_id), array_agg(value) from total where group by 1

will make it group by year. I couldn’t interpret the results as I don’t have a minimal POC to experiment with it.