Can the subquery be simplified/optimized?
Yes, you can refer to rows of higher levels in a subquery. But that would not solve your problem since you need to consider the whole table, not just the current row.

It's not completely clear what the query is trying to achieve and data types (table definition!) are also missing. Be aware that your invocation of generate_series() returns timestamptz, which is cast to date (I assume) and this cast depends on your current timezone setting. Results for the same query may be different with a different setting for the calling session.

It's a bit cleaner and cheaper to work with timestamp to begin with. See:


Generating time series between two dates in PostgreSQL


And NOT IN (&lt;subquery&gt;) is almost always a bad choice. There are faster options which are less error-prone. NOT IN carries traps for NULL values.


Select rows which are not present in other table


Each of these queries should be faster. The winner depends on data distribution in your table. Test both:

Query 1 (returns text like your original):

SELECT to_char(day, 'YYYY-MM-DD')
FROM   generate_series(timestamp '2015-12-20', LOCALTIMESTAMP, interval '1 day') d(day)
WHERE  NOT EXISTS (SELECT 1 FROM records WHERE day = d.day);


Query 2 (returns date):

SELECT generate_series(timestamp '2015-12-20', LOCALTIMESTAMP, '1 day')::date AS day
EXCEPT ALL
SELECT day FROM records;   -- data types must match