forecast_view.sql

-- Old table with types that are too big
create table forecast_base_0 (
   key_id bigint not null,
   time timestamp without timezone not null,
   value double precision,
   primary key (key_id, time)
);

-- New table with smaller types
create table forecast_base_1 (
  key_id integer not null,
  time date not null,
  value double precision,
  primary key (key_id, time)
);

-- View to provide a uniform API to both tables
CREATE OR REPLACE VIEW forecast AS 
 SELECT forecast_base_0.key_id::integer as value, forecast_base_0.time::date as value, forecast_base_0.value::real as value
 UNION ALL
 SELECT forecast_base_1.key_id, forecast_base_1.time, forecast_base_1.value  
;

-- Problematic query:
EXPLAIN SELECT * FROM forecast WHERE key_id = 123;

 Append  (cost=0.00..525161512.14 rows=57509925 width=611)
  ->  Seq Scan on forecast_base_0  (cost=0.00..0.00 rows=1 width=566)
         Filter: ((source_id)::integer = 123)
   -> Index Scan using forecast_base_1_pkey on forecast_base_1  (cost=0.43..8.39 rows=1 width=634)
         Index Cond: (source_id = 123)

-- Whereas:
EXPLAIN SELECT * FROM forecast_base_0 WHERE key_id = 123;

   Index Scan using forecast_base_0_pkey on forecast_base_0  (cost=0.43..8.39 rows=1 width=634)
      Index Cond: (source_id = 123)