From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: MVIEW refresh consistently faster then insert ... select |
Date: | 2016-05-04 07:24:11 |
Message-ID: | ngc82r$gta$1@ger.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Kevin Grittner schrieb am 04.05.2016 um 09:06:
> On Wed, May 4, 2016 at 1:46 AM, Thomas Kellerer <spam_eater(at)gmx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Travers | 2016-05-04 07:28:39 | Re: Thoughts on "Love Your Database" |
Previous Message | Kevin Grittner | 2016-05-04 07:06:41 | Re: MVIEW refresh consistently faster then insert ... select |