From: | Arjun Ranade <ranade(at)nodalexchange(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes |
Date: | 2018-09-27 17:08:05 |
Message-ID: | CANrrCRxsC3wr711bjAJ4zK_16vF-qQHZXjSJTstbQt9kG5QDSw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I have a strange performance situation that I cannot resolve with my usual
process.
I have a SELECT statement that completes in about 12 seconds for the full
result (~1100 rows).
If I create an empty table first, and then INSERT with the SELECT query, it
takes 6.5 minutes.
When I look at the EXPLAIN ANALYZE output, it seems that it's using a
drastically different query plan for the INSERT+SELECT than SELECT by
itself.
Here's the explain plan for the SELECT() by itself:
https://explain.depesz.com/s/8Qmr
Here's the explain plan for INSERT INTO x SELECT():
https://explain.depesz.com/s/qifT
I am running Postgresql 10(PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled
by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit).
Shared Buffers = 4gb
effective_cache_size = 4gb
work_mem = 8gb
wal_buffers = -1
max_wal_sze = 2gb
wal_level = replica
archiving on
Total RAM on machine: 252GB
This machine is VACUUM FULL,ANALYZE once a week. Autovac is ON with PG10
default settings.
The machine has 12 Intel(R) Xeon(R) CPU E5-2643 v3 @ 3.40GHz, and 15k RPM
disks for Postgres. I have tested write speed to all filesystems and
speeds are as expected. The pg_wal is on a separate disk resource,
however, these disks are also 15k in speed and setup the same way as
Postgres data disks.
The queries are sensitive so I had to obfuscate them in the explain plans.
I am reluctant to provide full metadata for all the objects involved, but
will if it comes to that. I first want to understand why the query plan
would be so different for a SELECT vs INSERT into X SELECT. I also tried
CREATE TABLE x as SELECT() but it also takes 6+ minutes.
Is there any advice as to the general case on why SELECT can finish in
10seconds but CREATE TABLE as SELECT() runs in 7 minutes?
Any advice would be much appreciated.
Thanks,
Arjun Ranade
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-09-27 17:21:34 | Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes |
Previous Message | Fabio Pardi | 2018-09-27 09:25:58 | Re: Why could different data in a table be processed with different performance? |