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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux