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?
Just to confirm, is this with or without the CONCURRENTLY keyword
on the REFRESH command?
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.
If *with*, I would guess that it is because most of the work is
done in temporary files and workspace, with just the delta applied
to the table and index in permanent storage.
It's hard to guess which way will be faster for the use case you
describe -- it will probably depend on what percentage of rows
remain unchanged on each REFRESH.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
