Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- with int

CREATE TABLE measured (
  id int,
  ts timestamp,
  identifier int,
  measured_value int
);
CREATE INDEX ON measured(identifier);

CREATE TABLE estimated (
  id int,
  ts timestamp,
  identifier int,
  estimated_value int
);
CREATE INDEX ON estimated(identifier);

CREATE VIEW combined AS
SELECT ts
     , identifier
     , m.measured_value
     , e.estimated_value
FROM   measured m
FULL   JOIN estimated e USING (identifier, ts);

CREATE TABLE

CREATE INDEX

CREATE TABLE

CREATE INDEX

CREATE VIEW

SET enable_seqscan = off;

SET

EXPLAIN
SELECT *
FROM   combined
WHERE  identifier = 1;

QUERY PLAN

Hash Full Join  (cost=18.71..28.93 rows=8 width=20)

  Hash Cond: ((m.identifier = e.identifier) AND (m.ts = e.ts))

  ->  Bitmap Heap Scan on measured m  (cost=4.21..14.37 rows=8 width=16)

        Recheck Cond: (identifier = 1)

        ->  Bitmap Index Scan on measured_identifier_idx  (cost=0.00..4.21 rows=8 width=0)

              Index Cond: (identifier = 1)

  ->  Hash  (cost=14.37..14.37 rows=8 width=16)

        ->  Bitmap Heap Scan on estimated e  (cost=4.21..14.37 rows=8 width=16)

              Recheck Cond: (identifier = 1)

              ->  Bitmap Index Scan on estimated_identifier_idx  (cost=0.00..4.21 rows=8 width=0)

                    Index Cond: (identifier = 1)

EXPLAIN

EXPLAIN
SELECT ts
     , identifier
     , m.measured_value
     , e.estimated_value
FROM   measured m
FULL   JOIN estimated e USING (identifier, ts)
WHERE  COALESCE(m.identifier, e.identifier) = 1;

QUERY PLAN

Hash Full Join  (cost=18.71..28.93 rows=8 width=20)

  Hash Cond: ((m.identifier = e.identifier) AND (m.ts = e.ts))

  ->  Bitmap Heap Scan on measured m  (cost=4.21..14.37 rows=8 width=16)

        Recheck Cond: (identifier = 1)

        ->  Bitmap Index Scan on measured_identifier_idx  (cost=0.00..4.21 rows=8 width=0)

              Index Cond: (identifier = 1)

  ->  Hash  (cost=14.37..14.37 rows=8 width=16)

        ->  Bitmap Heap Scan on estimated e  (cost=4.21..14.37 rows=8 width=16)

              Recheck Cond: (identifier = 1)

              ->  Bitmap Index Scan on estimated_identifier_idx  (cost=0.00..4.21 rows=8 width=0)

                    Index Cond: (identifier = 1)

EXPLAIN

-- only seems to work with COALESCE and FULL OUTER JOIN
EXPLAIN
SELECT ts
     , identifier
     , m.measured_value
     , e.estimated_value
FROM   measured m
FULL   JOIN estimated e USING (identifier, ts)
WHERE  m.identifier = 1 OR e.identifier = 1;

QUERY PLAN

Merge Full Join  (cost=321.74..348.32 rows=17 width=20)

  Merge Cond: ((m.identifier = e.identifier) AND (m.ts = e.ts))

  Filter: ((m.identifier = 1) OR (e.identifier = 1))

  ->  Sort  (cost=160.87..165.12 rows=1700 width=16)

        Sort Key: m.identifier, m.ts

        ->  Index Scan using measured_identifier_idx on measured m  (cost=0.15..69.65 rows=1700 width=16)

  ->  Sort  (cost=160.87..165.12 rows=1700 width=16)

        Sort Key: e.identifier, e.ts

        ->  Index Scan using estimated_identifier_idx on estimated e  (cost=0.15..69.65 rows=1700 width=16)

EXPLAIN

-- only seems to work with COALESCE and FULL OUTER JOIN
EXPLAIN
SELECT ts
     , identifier
     , m.measured_value
     , e.estimated_value
FROM   measured m
JOIN   estimated e USING (identifier, ts)
WHERE  COALESCE(m.identifier, e.identifier) = 1;

QUERY PLAN

Merge Join  (cost=321.74..348.14 rows=1 width=20)

  Merge Cond: ((m.identifier = e.identifier) AND (m.ts = e.ts))

  Join Filter: (COALESCE(m.identifier, e.identifier) = 1)

  ->  Sort  (cost=160.87..165.12 rows=1700 width=16)

        Sort Key: m.identifier, m.ts

        ->  Index Scan using measured_identifier_idx on measured m  (cost=0.15..69.65 rows=1700 width=16)

  ->  Sort  (cost=160.87..165.12 rows=1700 width=16)

        Sort Key: e.identifier, e.ts

        ->  Index Scan using estimated_identifier_idx on estimated e  (cost=0.15..69.65 rows=1700 width=16)

EXPLAIN

-- only seems to work with COALESCE and FULL OUTER JOIN
EXPLAIN
SELECT ts
     , identifier
     , m.measured_value
     , e.estimated_value
FROM   measured m
JOIN   estimated e USING (identifier, ts)
WHERE  m.identifier = 1 OR e.identifier = 1;

QUERY PLAN

Merge Join  (cost=321.74..348.32 rows=1 width=20)

  Merge Cond: ((m.identifier = e.identifier) AND (m.ts = e.ts))

  Join Filter: ((m.identifier = 1) OR (e.identifier = 1))

  ->  Sort  (cost=160.87..165.12 rows=1700 width=16)

        Sort Key: m.identifier, m.ts

        ->  Index Scan using measured_identifier_idx on measured m  (cost=0.15..69.65 rows=1700 width=16)

  ->  Sort  (cost=160.87..165.12 rows=1700 width=16)

        Sort Key: e.identifier, e.ts

        ->  Index Scan using estimated_identifier_idx on estimated e  (cost=0.15..69.65 rows=1700 width=16)

EXPLAIN