PG 9.3 materialized view VS Views, indexes, shared memory

From: Nicolas Paris <niparisco(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: PG 9.3 materialized view VS Views, indexes, shared memory
Date: 2015-02-20 10:28:27
Message-ID: CA+ssMOQSXAa5uOLZSeanUNtC1YuSVZfoovX73PxU_MQO4o59Hg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello !

I have a huge table, 1 bilion rows, with many indexes.
I have many materialysed view (MV), subsets of this huge table, with same
kind indexes.
I have many users, querying thoses MV.
I have a storage problem, because of MV physical memory use.

I wonder :
If I replace MV with classical Views, the only indexes that will be used
will be the huge table's one. As all users will query on the same indexes,
is will always be loaded in memory, right ? This will be shared, I mean if
10 users query the same time, will it use 10*ram memory for indexes or
juste 1 time that ram ?

I terms of performances, will MV better than simple Views in my case ?

Thanks for explanation by advance

Nicolas PARIS

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Matheus de Oliveira 2015-02-20 12:36:40 Re: PG 9.3 materialized view VS Views, indexes, shared memory
Previous Message Tomas Vondra 2015-02-19 03:08:33 Re: PATCH: adaptive ndistinct estimator v3 (WAS: Re: [PERFORM] Yet another abort-early plan disaster on 9.3)