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 <nipari...@gmail.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.math...@gmail.com>
> :
>
>>
>>
>> On Fri, Feb 20, 2015 at 11:06 AM, Nicolas Paris <nipari...@gmail.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
>>
>>
>
