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

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(dot)matheus(at)gmail(dot)com>:

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