Query efficiency with sub-query defined time ranges / event table

TimescaleDB and PostgreSQL
1

Hi,

I have a fairly large hypertable (t) with a few thousand chunks. I hacked together an events table (e) which itself holds start_time and end_time values which correspond to ranges of time in t.
I’m looking for a way to select an event from e and query data from t in a single statement, but the dilemma is everything I try results in a sequential scan of every chunk in t (which is slow).

So far I’ve tried:

  • Materialized and un-materialized CTEs
  • Subqueries
  • JOIN ON statements

t is indexed on time, while e is indexed on both start_time and end_time.

Is there a way to force the resolution of the time bounds from e, and therefore skip irrelevant chunks in t?

As a sanity check, I can query against t with ISO timestamps cast to timestamptz and I get great performance.

pg version: 15
ts version: 2.16

2

Hi all,

I was able to solve this reasonably well by defining a PL/pgsql function that I call instead. The function forces the resolution of the timestamps I want before querying against the larger table. Let me know if you want to see example code.