Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes

From: Vladimir Ryabtsev <greatvovan(at)gmail(dot)com>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: ranade(at)nodalexchange(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes
Date: 2018-09-27 23:50:36
Message-ID: CAMqTPqkMHOBzNBqxsOQenEjBCs4vA7pevt0BLZc3CX9hY8ypUg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

> It did --- see the Gather node.
But "workers launched: 1"...
To my opinion, such a dramatic difference cannot be explained with avoiding
parallelism, the query was just stuck in a very inefficient plan (even
though almost all source data is read from cache).

Additionally, I think author can try CREATE STATISTICS on the bunch of
columns used in join. Very low rows estimate for this join may come from
multiplying selectivities for each column assuming they are independent.

Vlad

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Justin Pryzby 2018-09-28 00:12:08 Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes
Previous Message Tom Lane 2018-09-27 20:41:29 Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes