Re: MVIEW refresh consistently faster then insert ... select

Kevin Grittner schrieb am 04.05.2016 um 09:06:
> On Wed, May 4, 2016 at 1:46 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
>
>> I have a table that is an aggregation of another table.
>> This aggregation reduces an input of ~14 million rows to ~4
>> million rows.
>
>> The refresh takes approx 2 minutes (fastest was 1:40) on our
>> development server (CentOS, Postgres 9.5.0)
>
>> However, when I create a materialized view:
>
>> Subsequent refreshs using "REFRESH MATERIALIZED VIEW mv_stock"
>> are consistently much faster: between 40 seconds and 1 minute
>>
>> I have run both refreshs about 10 times now, so caching effects
>> should not be there.
>>
>> My question is: what is refresh mview doing differently then a
>> plain insert ... select that it makes that so much faster?
>
> If *without*, I would guess the difference is probably in creating
> the index "from scratch" with sort and load versus retail insertion
> of index entries.  You could approximate this by dropping the index
> before the TRUNCATE and INSERT and creating it again after it is
> loaded.

It's without.

But your suggestion got me looking in the right direction ;)

I completely forgot that the table has two foreign keys that I did not create on the mview.

When I remove those, then both solutions are equally fast.

Sorry for the noise.

Thomas