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