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

On Fri, Feb 20, 2015 at 8:28 AM, Nicolas Paris <niparisco@gmail.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