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

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

 



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.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



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

  Powered by Linux