PostgreSQL: Why is CREATE TABLE AS faster than CREATE ... INSERT INTO?

Question

Below are the two different syntaxs for the same thing.

With a COPY TABLE AS SELECT (CTAS).

CREATE TABLE main
AS
  SELECT *
  FROM other;

As separate statements with CREATE TABLE and INSERT INTO

CREATE TABLE main (like other);

INSERT INTO main
SELECT *
FROM other;

I have observed that the CTAS is faster than distinct CREATE TABLE .. INSERT. The first takes 20 seconds to complete execution. The second syntax two takes 1 min 15 secs to complete execution.

What could be the reason for difference?

Welcome to [dba.se]. First stop using [`SELECT INTO` instead use `CREATE TABLE AS`](https://dba.stackexchange.com/q/156105/2639) Also, put your name in your profile "Sanjay Salunkhe" not in the question =) — NO WAR WITH RUSSIA, Oct 26 '17 at 17:43

NO WAR WITH RUSSIA · Answer 1 · 2017-10-26T18:49:10.613

CREATE TABLE AS has some advantages over the other form, namely in the reduction or elimination of WAL.. Certainly optiimizations can be applied to a few commands (viz. CREATE TABLE AS, CREATE INDEX, CLUSTER, COPY into tables that were created or truncated in the same transaction) in certain wal-reduction modes (minimal).

In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7).

But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so replica or higher must be used to enable WAL archiving (archive_mode) and streaming replication.

You can read more information about this here

14.4.7. Disable WAL Archival and Streaming Replication

When loading large amounts of data into an installation that uses WAL archiving or streaming replication, it might be faster to take a new base backup after the load has completed than to process a large amount of incremental WAL data. To prevent incremental WAL logging while loading, disable archiving and streaming replication, by setting wal_level to minimal, archive_mode to off, and max_wal_senders to zero. But note that changing these settings requires a server restart.

Aside from avoiding the time for the archiver or WAL sender to process the WAL data, doing this will actually make certain commands faster, because they are designed not to write WAL at all if wal_level is minimal. (They can guarantee crash safety more cheaply by doing an fsync at the end than by writing WAL.)

As a special note, if you go back to your

CREATE TABLE t1 (like t2);

And instead do

CREATE TABLE UNLOGGED t1 (like t2);

It'll be even faster. But, don't do this as you'll be missing WAL. For more information on this read

PostgreSQL: Why is CREATE TABLE AS faster than CREATE ... INSERT INTO?

1 Answers1