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

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