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

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: Vladimir Ryabtsev <greatvovan(at)gmail(dot)com>
Cc: tgl(at)sss(dot)pgh(dot)pa(dot)us, 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-28 00:12:08
Message-ID: 20180928001208.GL776@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Thu, Sep 27, 2018 at 04:50:36PM -0700, Vladimir Ryabtsev wrote:
> 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.

MV statistics don't currently help for joins:
https://www.postgresql.org/message-id/flat/CAKJS1f-6B7KnDFrh6SFhYn-YbHYOXmDDAfd0XC%3DjJKZMCrfQyg%40mail.gmail.com#925e19951fabc9a480b804d661d83be8

Justin

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Sam R. 2018-09-28 04:45:25 Re: To keep indexes in memory, is large enough effective_cache_size enough?
Previous Message Vladimir Ryabtsev 2018-09-27 23:50:36 Re: SELECT statement returns in 10seconds, but INSERT/CREATE TABLE AS with same SELECT takes 7 minutes