Kevin Grittner schrieb am 04.05.2016 um 09:06:
> On Wed, May 4, 2016 at 1:46 AM, Thomas Kellerer <spam_ea...@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





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to