BEGIN;
-- A table of measured values with an index on identifier
DROP TABLE IF EXISTS measured CASCADE;
CREATE TABLE
measured
(
  id int,
  ts timestamp,
  identifier text,
  measured_value int
)
;
CREATE INDEX ON measured(identifier);

-- A table of estimated values with an index on identifier
DROP TABLE IF EXISTS estimated CASCADE;
CREATE TEMPORARY TABLE
estimated
(
  id int,
  ts timestamp,
  identifier character varying,
  estimated_value int
)
;
CREATE INDEX ON estimated(identifier);

-- Delete any existing test data
TRUNCATE TABLE measured;
TRUNCATE TABLE estimated;

-- Generate lots of random data
INSERT INTO measured
SELECT
generate_series(1, 1000000),
to_timestamp((random() * 100000)::int),
left(md5(random()::text), 2),
random() * 10
;
INSERT INTO estimated
SELECT
generate_series(1, 1000000),
to_timestamp((random() * 100000)::int),
left(md5(random()::text), 2),
random() * 10
;

-- A view to look at for estimated and measured values
-- for the same timestamp and identifier
DROP VIEW IF EXISTS combined;
CREATE TEMPORARY VIEW
combined
AS
SELECT
COALESCE(measured.ts, estimated.ts) AS ts,
COALESCE(measured.identifier, estimated.identifier) AS identifier,
measured_value,
estimated_value
FROM
measured
FULL OUTER JOIN
estimated
ON
measured.identifier = estimated.identifier
AND
measured.ts = estimated.ts
;

EXPLAIN ANALYZE
SELECT
*
FROM
combined
WHERE
identifier = 'ab'
;

ROLLBACK;