timescale_join_issue.md

Schema for Postgres postgres:11

create table test_table_1 (
time timestamptz default now(),
id int,
name text
);
create table test_table_2 (
time timestamptz default now(),
id int,
name text
);


insert into test_table_1 values(now(), 1, 'a');
insert into test_table_1 values(now(), 2, 'b');
insert into test_table_2 values(now(), 1, 'a');

Schema for timescale timescale/timescaledb:1.4.1-pg11-oss

create table test_table_1 (
time timestamptz default now(),
id int,
name text
);
create table test_table_2 (
time timestamptz default now(),
id int,
name text
);

select create_hypertable('test_table_1', 'time');
select create_hypertable('test_table_2', 'time');

insert into test_table_1 values(now(), 1, 'a');
insert into test_table_1 values(now(), 2, 'b');
insert into test_table_2 values(now(), 1, 'a');

Query made on Postgres:

postgres=# select one.id, two.name from test_table_1 one left outer join test_table_2 two on one.id=two.id where one.id=2;
 id | name 
----+------
  2 | 
(1 row)

On timescale:

postgres=# select one.id, two.name from test_table_1 one left outer join test_table_2 two on one.id=two.id where one.id=2;
 id | name 
----+------
(0 rows)

Query plan on Postgres:

postgres=# explain analyze select one.id, two.name from test_table_1 one left outer join test_table_2 two on one.id=two.id where one.id=2;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..48.81 rows=36 width=36) (actual time=0.055..0.056 rows=1 loops=1)
   Join Filter: (one.id = two.id)
   ->  Seq Scan on test_table_1 one  (cost=0.00..24.12 rows=6 width=4) (actual time=0.013..0.013 rows=1 loops=1)
         Filter: (id = 2)
         Rows Removed by Filter: 1
   ->  Materialize  (cost=0.00..24.16 rows=6 width=36) (actual time=0.040..0.040 rows=0 loops=1)
         ->  Seq Scan on test_table_2 two  (cost=0.00..24.12 rows=6 width=36) (actual time=0.004..0.004 rows=0 loops=1)
               Filter: (id = 2)
               Rows Removed by Filter: 1
 Planning Time: 0.112 ms
 Execution Time: 0.102 ms
(11 rows)

Query plan on timescale:

postgres=# explain analyze select one.id, two.name from test_table_1 one left outer join test_table_2 two on one.id=two.id where one.id=2;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..48.96 rows=6 width=36) (actual time=0.022..0.022 rows=0 loops=1)
   Join Filter: (one.id = two.id)
   Filter: (two.id = 2)
   Rows Removed by Filter: 1
   ->  Append  (cost=0.00..24.16 rows=6 width=4) (actual time=0.013..0.014 rows=1 loops=1)
         ->  Seq Scan on _hyper_1_1_chunk one  (cost=0.00..24.12 rows=6 width=4) (actual time=0.012..0.013 rows=1 loops=1)
               Filter: (id = 2)
               Rows Removed by Filter: 1
   ->  Materialize  (cost=0.00..24.19 rows=6 width=36) (actual time=0.006..0.007 rows=0 loops=1)
         ->  Append  (cost=0.00..24.16 rows=6 width=36) (actual time=0.005..0.005 rows=0 loops=1)
               ->  Seq Scan on _hyper_2_2_chunk two  (cost=0.00..24.12 rows=6 width=36) (actual time=0.004..0.004 rows=0 loops=1)
                     Filter: (id = 2)
                     Rows Removed by Filter: 1
 Planning Time: 0.770 ms
 Execution Time: 0.055 ms
(15 rows)

The behaviour is exactly same as postgres on timescale if the table is not a hypertable.