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

Thanks,

I like the idea of partial indexes mixed with simple Views
So question :

huge_table{
id,
field
}
CREATE INDEX idx_huge_table ON huge_table(id)
CREATE INDEX idx_huge_table_for_view1 ON huge_table(id) WHERE id IN (1,2,3)

CREATE VIEW view1 AS SELECT * FROM huge_table WHERE id IN (1,2,3)

Do the following query uses idx_huge_table_for_view1 ?
SELECT * FROM view1
WHERE field LIKE 'brillant idea'

In other words, do all queries on view1 will use the partial index (and
never the idx_hute_table ) ?

Nicolas PARIS

2015-02-20 13:36 GMT+01:00 Matheus de Oliveira <matioli(dot)matheus(at)gmail(dot)com>:

>
> 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
>
>