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 they are doing should be exactly the same.
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
