[Bug]: Chunk Exclusion does not work in Lateral Joins with Hypertables. Makes hasura subscriptions unusable

[lovasoa]

What type of bug is this?

Performance issue

What subsystems and features are affected?

Query planner

What happened?

The following simple query is slow

select * 
from unnest(array[123456]::integer[]) as vars(v) 
left outer join lateral (
  select * from conditions where id = vars.v
) on (true)

When performing a lateral join between an unnested array and a hypertable using the primary key, TimescaleDB's chunk exclusion optimization fails to work properly. The query planner scans all chunks in the hypertable instead of using constraint exclusion to scan only the relevant chunks.

Expected behavior

TimescaleDB should use its chunk exclusion logic to identify and scan only the chunks that could possibly contain the ID value being searched for (in this case, ID=123456), similar to how it works with regular WHERE clauses.

Actual behavior

The query plan shows that PostgreSQL/TimescaleDB performs index scans on thousands of chunks, even though only one (or none) could possibly contain the requested ID. This results in significant performance degradation:

• Thousands of unnecessary index scans
• Query time of multiple seconds in our production db
• Linear performance degradation as the number of chunks increases

Additional Context

This issue is particularly problematic because hasura graphql always uses lateral joins when managing subscriptions: https://github.com/hasura/graphql-engine/blob/master/server/src-lib/Hasura/GraphQL/Execute/Subscription/Plan.hs#L5

The problem appears to be that TimescaleDB's chunk exclusion logic doesn't properly analyze the lateral join condition to determine that vars.v has a specific value that could be used for chunk pruning.

• This affects Hasura's ability to efficiently query TimescaleDB hypertables

Workaround

Currently, the only workaround is not to use hasura graphql subscription and only make static queries.

Impact

High - This significantly impacts query performance for common patterns when using Hasura GraphQL with TimescaleDB.

TimescaleDB version affected

2.22.0

PostgreSQL version used

17

What operating system did you use?

official docker image

What installation method did you use?

Docker

What platform did you run on?

Not applicable

Relevant log output and stack trace

postgres=# explain analyze select * from unnest(array[123456]::integer[]) as vars(v) left outer join lateral (select * from conditions where id = vars.v) on (true);
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.28..41545.70 rows=25006 width=31) (actual time=2.932..66.588 rows=1 loops=1)
   ->  Function Scan on unnest vars  (cost=0.00..0.01 rows=1 width=4) (actual time=0.028..0.031 rows=1 loops=1)
   ->  Append  (cost=0.28..41495.68 rows=5001 width=27) (actual time=1.860..65.511 rows=1 loops=1)
         ->  Index Scan using "1_1_conditions_pkey" on _hyper_1_1_chunk  (cost=0.28..8.29 rows=1 width=27) (actual time=0.044..0.045 rows=0 loops=1)
               Index Cond: (id = vars.v)
         ->  Index Scan using "2_2_conditions_pkey" on _hyper_1_2_chunk  (cost=0.28..8.29 rows=1 width=27) (actual time=0.025..0.026 rows=0 loops=1)
               Index Cond: (id = vars.v)
         ->  Index Scan using "3_3_conditions_pkey" on _hyper_1_3_chunk  (cost=0.28..8.29 rows=1 width=27) (actual time=0.018..0.019 rows=0 loops=1)
               Index Cond: (id = vars.v)
         ->  Index Scan using "4_4_conditions_pkey" on _hyper_1_4_chunk  (cost=0.28..8.29 rows=1 width=27) (actual time=0.016..0.016 rows=0 loops=1)
               Index Cond: (id = vars.v)
         ->  ...thousands more index scans
Time: 1129.191 ms (00:01.129)

How can we reproduce the bug?

1. Create a hypertable with an integer primary key:

CREATE TABLE conditions (
    id INTEGER PRIMARY KEY,
    value text DEFAULT 'hello worl blah blah !'
);

SELECT create_hypertable('conditions', 'id', chunk_time_interval => 1000); -- simulating a case with many chunks

COPY conditions (id) FROM PROGRAM 'seq 5000000' WITH (FORMAT csv);

2. Run the problematic query:

-- This query scans ALL chunks instead of using chunk exclusion
EXPLAIN ANALYZE 
SELECT * FROM unnest(array[123456]::integer[]) as vars(v) 
LEFT OUTER JOIN LATERAL (
    SELECT * FROM conditions WHERE id = vars.v
) ON (true);

3. Compare with a direct query that properly uses chunk exclusion:

-- This query should scan only relevant chunks
EXPLAIN ANALYZE 
SELECT * FROM conditions WHERE id = 123456;

[pnthao]

@lovasoa Thank you for your detailed report of the issue, and for finding #4783! We are looking into it to see if we can have a fix.