MVIEW refresh consistently faster then insert ... select - Mailing list pgsql-general

Hello,

I have a table that is an aggregation of another table.
This aggregation reduces an input of ~14 million rows to ~4 million rows.

So far I have used a truncate/insert approach for this:

    truncate table stock;
    insert into stock (product_id, warehouse_id, reserved_provisional, reserved, available, non_deliverable)
    select product_id, warehouse_id, sum(reserved_provisional), sum(reserved), sum(available), sum(non_deliverable)
    from staging.stock_data
    group by product_id, warehouse_id;

The table has a primary key on (product_id, warehouse_id), all columns are integer columns.
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:

    create materialized view mv_stock
    as
    select product_id,
           warehouse_id,
           sum(reserved_provisional) as reserved_provisional,
           sum(reserved) as reserved,
           sum(available) as available,
           sum(non_deliverable) as non_deliverable
    from staging.stock_data
    group by product_id, warehouse_id;

    create unique index pk_mv_stock on mv_stock (product_id, warehouse_id);

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? 

The select itself takes about 25 seconds. It is using an external merge on disk, which can be removed by increasing
work_mem(the select then goes down to 12 seconds, but that doesn't change much in the runtime of the refreshs).  

The 2 minutes are perfectly acceptable, I'm just curious why refreshing the mview would be so much faster as the work
theyare doing should be exactly the same.  

Thomas