CREATE TABLE WITH NO DATA is very slow

user_3579379 0
Viewed 218

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?

postgresql
1 Answers

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);
user_64643080
Related