From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Nicolas Paris <niparisco(at)gmail(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: PGSQL 9.3 - Materialized View - multithreading |
Date: | 2014-04-04 16:54:07 |
Message-ID: | CAA-aLv7S5XtJX-k4jYP1iMQVh3d0+qS7T4EtwZJ8D3zU3pSpiA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 4 April 2014 17:29, Nicolas Paris <niparisco(at)gmail(dot)com> wrote:
> Hello,
>
> My question is about multiprocess and materialized View.
> http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html
> I (will) have something like 3600 materialised views, and I would like to
> know the way to refresh them in a multithread way
> (anderstand 8 cpu cores -> 8 refresh process in the same time)
The only thing that immediately comes to mind would be running a
rather hacky DO function in 4 separate sessions:
DO $$
DECLARE
session CONSTANT BIGINT := 0;
rec RECORD;
BEGIN
FOR rec IN SELECT quote_ident(nspname) || '.' ||
quote_ident(relname) AS mv FROM pg_class c INNER JOIN pg_namespace n
ON c.relnamespace = n.oid WHERE relkind = 'm' AND c.oid::bigint % 8 =
session LOOP
RAISE NOTICE 'Refreshing materialized view: %', rec.mv;
EXECUTE 'REFRESH MATERIALIZED VIEW ' || rec.mv || ';';
END LOOP;
END$$ language plpgsql;
Where you would set session to 0 for the first session, 1 for the
next, 2 for the next and 3 for the next, and so on until you reach 7
for the last. These would each be run in a separate parallel session,
although someone may come up with a better solution.
--
Thom
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2014-04-04 17:59:03 | Re: Fwd: Slow Count-Distinct Query |
Previous Message | Nicolas Paris | 2014-04-04 16:29:42 | PGSQL 9.3 - Materialized View - multithreading |