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

Well it seems that max query size for CREATE INDEX is 8160 character in my
9.3 postgresql version.
Then the only solution see is to add a new boolean field : huge_table.view1
and change predicat to "WHERE view1=1 "
But I may have 800 views.. adding 800 new fields indexed to the huge table
is actually not a good idea. Too bad

Any idea to solve that partial view limitation?

Nicolas PARIS

2015-02-20 17:19 GMT+01:00 Nicolas Paris <niparisco(at)gmail(dot)com>:

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