Postgres 13

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

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

CREATE TABLE estimated (
  id int
, ts timestamp
, identifier text
, 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 = 'ab';

QUERY PLAN

Hash Full Join  (cost=16.93..25.45 rows=5 width=48)

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

  ->  Bitmap Heap Scan on measured m  (cost=4.19..12.66 rows=5 width=44)

        Recheck Cond: (identifier = 'ab'::text)

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

              Index Cond: (identifier = 'ab'::text)

  ->  Hash  (cost=12.66..12.66 rows=5 width=44)

        ->  Bitmap Heap Scan on estimated e  (cost=4.19..12.66 rows=5 width=44)

              Recheck Cond: (identifier = 'ab'::text)

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

                    Index Cond: (identifier = 'ab'::text)

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) = 'ab';

QUERY PLAN

Hash Full Join  (cost=16.93..25.45 rows=5 width=48)

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

  ->  Bitmap Heap Scan on measured m  (cost=4.19..12.66 rows=5 width=44)

        Recheck Cond: (identifier = 'ab'::text)

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

              Index Cond: (identifier = 'ab'::text)

  ->  Hash  (cost=12.66..12.66 rows=5 width=44)

        ->  Bitmap Heap Scan on estimated e  (cost=4.19..12.66 rows=5 width=44)

              Recheck Cond: (identifier = 'ab'::text)

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

                    Index Cond: (identifier = 'ab'::text)

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 = 'ab' OR e.identifier = 'ab';

QUERY PLAN

Merge Full Join  (cost=228.08..244.57 rows=11 width=48)

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

  Filter: ((m.identifier = 'ab'::text) OR (e.identifier = 'ab'::text))

  ->  Sort  (cost=114.04..116.72 rows=1070 width=44)

        Sort Key: m.identifier, m.ts

        ->  Index Scan using measured_identifier_idx on measured m  (cost=0.15..60.20 rows=1070 width=44)

  ->  Sort  (cost=114.04..116.72 rows=1070 width=44)

        Sort Key: e.identifier, e.ts

        ->  Index Scan using estimated_identifier_idx on estimated e  (cost=0.15..60.20 rows=1070 width=44)

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) = 'ab';

QUERY PLAN

Merge Join  (cost=0.30..237.58 rows=1 width=48)

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

  Join Filter: ((COALESCE(m.identifier, e.identifier) = 'ab'::text) AND (m.ts = e.ts))

  ->  Index Scan using measured_identifier_idx on measured m  (cost=0.15..60.20 rows=1070 width=44)

  ->  Materialize  (cost=0.15..62.88 rows=1070 width=44)

        ->  Index Scan using estimated_identifier_idx on estimated e  (cost=0.15..60.20 rows=1070 width=44)

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 = 'ab' OR e.identifier = 'ab';

QUERY PLAN

Merge Join  (cost=228.08..244.57 rows=1 width=48)

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

  Join Filter: ((m.identifier = 'ab'::text) OR (e.identifier = 'ab'::text))

  ->  Sort  (cost=114.04..116.72 rows=1070 width=44)

        Sort Key: m.identifier, m.ts

        ->  Index Scan using measured_identifier_idx on measured m  (cost=0.15..60.20 rows=1070 width=44)

  ->  Sort  (cost=114.04..116.72 rows=1070 width=44)

        Sort Key: e.identifier, e.ts

        ->  Index Scan using estimated_identifier_idx on estimated e  (cost=0.15..60.20 rows=1070 width=44)

EXPLAIN