Re: PG 9.3 materialized view VS Views, indexes, shared memory

On Fri, Feb 20, 2015 at 8:28 AM, Nicolas Paris <niparisco(at)gmail(dot)com> wrote:

> 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 ?
>
>
Once one user load pages into the shared_buffer (or even OS memory cache),
subsequent users that requests the same pages will read from there (from
the memory), it is valid from pages of any kind of relation (MVs, tables,
indexes, etc.). So if 10 users use the same index, then the pages read from
it will be loaded in memory only once (unless it doesn't fit
ram/shared_buffer, of course).

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

We'd need a lot more of information to answer this question. I tend to
recommend people to try simpler approaches (in your case "simple views")
and only move to more robust ones if the performance of this one is bad.

By the little information you gave, looks like the queries gets a well
defined subset of this big table, so you should also consider:

- Creating partial indexes for the subsets, or at least the most accessed
ones;
- Partitioning the table (be really careful with that and make sure you
actually use the partition keys).

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres