Kevin Grittner schrieb am 04.05.2016 um 09:06: > On Wed, May 4, 2016 at 1:46 AM, Thomas Kellerer <spam_eater@xxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
