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

Поиск
Список
Период
Сортировка
От Matheus de Oliveira
Тема Re: PG 9.3 materialized view VS Views, indexes, shared memory
Дата
Msg-id CAJghg4+dS5+BDtw6PWGj72hB8YpivMiBGmynEof012pY4yfjQg@mail.gmail.com
обсуждение исходный текст
Ответ на PG 9.3 materialized view VS Views, indexes, shared memory  (Nicolas Paris <niparisco@gmail.com>)
Ответы Re: PG 9.3 materialized view VS Views, indexes, shared memory
Список pgsql-performance
Дерево обсуждения

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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Nicolas Paris
Дата:
Сообщение: PG 9.3 materialized view VS Views, indexes, shared memory
Следующее
От: Nicolas Paris
Дата:
Сообщение: Re: PG 9.3 materialized view VS Views, indexes, shared memory