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