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

[Matheus de Oliveira <matioli.matheus@xxxxxxxxx>]

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