It appears that in the predicate close (WHERE id IN (foo)), foo cannot depend on other table (join or other). It must be a list. I anderstand why (this must be static). I can build a string value, but in some case, I will have a milion key list. Postgresql do not have limitation in query size, and IN(...) keys number.
But creating a partial index, with a query of bilion character length is not an issue ? It looks like a little dirty, not ? Thanks for all Nicolas PARIS 2015-02-20 15:44 GMT+01:00 Matheus de Oliveira <matioli.math...@gmail.com>: > > > On Fri, Feb 20, 2015 at 11:06 AM, Nicolas Paris <nipari...@gmail.com> > wrote: > >> 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 ) ? >> >> > You can try that pretty easily: > > postgres=# CREATE TEMP TABLE huge_table(id int, field text); > CREATE TABLE > postgres=# CREATE INDEX huge_table_id_idx ON huge_table(id); > CREATE INDEX > postgres=# CREATE INDEX huge_table_id_partial_idx ON huge_table(id) > WHERE id IN (1,2,3); > CREATE INDEX > postgres=# CREATE TEMP VIEW view1 AS SELECT * FROM huge_table WHERE id > IN (1,2); > CREATE VIEW > postgres=# SET enable_seqscan TO off; > SET > postgres=# SET enable_bitmapscan To off; > SET > postgres=# EXPLAIN SELECT * FROM view1 WHERE field LIKE 'foo%'; > QUERY > PLAN > > ---------------------------------------------------------------------------------------------- > Index Scan using *huge_table_id_partial_idx* on huge_table > (cost=0.12..36.41 rows=1 width=36) > Index Cond: (id = ANY ('{1,2}'::integer[])) > Filter: (field ~~ 'foo%'::text) > (3 rows) > > I expect that to happen always, unless you have another index that matches > better the filter from outside the view. > > Regards, > -- > Matheus de Oliveira > Analista de Banco de Dados > Dextra Sistemas - MPS.Br nĂvel F! > www.dextra.com.br/postgres > >