On Fri, Feb 20, 2015 at 11:06 AM, Nicolas Paris <niparisco@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
