Fill missing dates within groups
You also have a table holding unique stations with identifiers, Could look like this:

CREATE TABLE uniq_stations (station_id text);
INSERT INTO uniq_stations VALUES
('001'),
('002'),
('003');


There will be more columns, which are irrelevant for us.

This should be much faster then:

SELECT station_id, s.value, date
FROM   uniq_stations u
CROSS  JOIN (
   SELECT generate_series (timestamp '2014-01-01'
                         , timestamp '2014-01-04'
                         , interval  '1 day')::date
   ) d(date)
LEFT   JOIN stations s USING (station_id, date)
ORDER  BY station_id, date;  -- optional


db&lt;>fiddle here

You do not need a LATERAL join at all, the date series is the same for every station. Only a CROSS JOIN to build the complete Cartesian product of stations and days, then a LEFT [OUTER] JOIN to existing combinations in table stations (an unfortunate table name for its content, btw.). LATERAL joins are great, when needed. But plain joins are faster.

Also, this fills in stations with all days missing, which would not work at all without uniq_stations. You may or may not have such cases.

One of the expensive pieces in this puzzle is identifying unique stations. A task we can skip completely if we can use the added uniq_stations providing what we need. Else we might use DISTINCT ON or a recursive CTE to make use of a matching index. See:


Optimize GROUP BY query to retrieve latest row per user
Select first row in each GROUP BY group?


Still more expensive than reading unique rows from a table, but much faster already than what you had. Which was a grand waste of CPU cycles, frankly.

Finally, a multicolumn index on stations (station_id, date) should deliver top notch performance even with a big table stations. (Using a higher percentage of rows from that table makes the index less important.)

There is a reason I use generate_series (timestamp, timestamp, interval):


Generating time series between two dates in PostgreSQL


Related:


Filling in missing dates in record set from generate_series()
Join a count query on a generate_series in postgres and also retrieve Null-values as “0”


Aside: Your station_id should probably type integer. Faster than text, and smaller, too if numbers go beyond 999.