Postgres 13

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

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

CREATE TABLE estimated (
  id int
, ts timestamp
, identifier varchar
, 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

Merge Full Join  (cost=228.08..244.50 rows=5 width=48)

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

  Filter: ((COALESCE(m.identifier, e.identifier))::text = '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

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

QUERY PLAN

Merge Full Join  (cost=228.08..244.50 rows=5 width=48)

  Merge Cond: (((measured.identifier)::text = (estimated.identifier)::text) AND (measured.ts = estimated.ts))

  Filter: ((COALESCE(measured.identifier, estimated.identifier))::text = 'ab'::text)

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

        Sort Key: measured.identifier, measured.ts

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

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

        Sort Key: estimated.identifier, estimated.ts

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

EXPLAIN