I'm running this SQL command on PostgreSQL 11:
CREATE TABLE IF NOT EXISTS my_temp_table AS TABLE my_enormous_table WITH NO DATA;
It takes 5 minutes to make the new table.
The EXPLAIN ... is:
Seq Scan on my_enormous_table (cost=0.00..35999196.34 rows=143407234 width=3278)
Moving to a query like CREATE TABLE ... (SELECT * FROM my_enormous_table WHERE FALSE); is orders of magnitude faster - there is no seq scan, and the outcome is the same.
Any ideas what could be causing this issue?
WITH NO DATA still executes the query, it just ignores the result.
The better way to do that would be to avoid CREATE TABLE ... AS:
CREATE TABLE my_temp_table (LIKE my_enormous_table);
That also allows you to use the INCLUDING clause to copy default values, storage parameters, constraints and other things from the original table:
CREATE TABLE my_temp_table (LIKE my_enormous_table
INCLUDING CONSTRAINTS INCLUDING DEFAULTS);