SELECT statement returns in 10seconds, but INSERT/CREATE TABLE ASwith same SELECT takes 7 minutes

Поиск
Список
Период
Сортировка
От Arjun Ranade
Тема SELECT statement returns in 10seconds, but INSERT/CREATE TABLE ASwith same SELECT takes 7 minutes
Дата
Msg-id CANrrCRxsC3wr711bjAJ4zK_16vF-qQHZXjSJTstbQt9kG5QDSw@mail.gmail.com
обсуждение исходный текст
Ответы Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes
Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLEAS with same SELECT takes 7 minutes
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Fabio Pardi
Дата:
Сообщение: Re: Why could different data in a table be processed with differentperformance?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes