Re: what do i need to know about array index?

On 20/07/10 18:27, Prometheus Prometheus wrote:

What's with the pseudonym?

> to my index problem:
> e.g. a query
> select id from test_1 where NOT (tag[4]=false OR tag[4] IS NULL);
>
> doesnt use the index
> create index idx_test_1 on test(( NOT (tag[4]=false OR tag[4] IS NULL) ));

You should be using:

tag[4] IS DISTINCT FROM 't';

if you want to index on 'true' vs 'false or null'. Your current
expression is buggy for null tag values, as can be seen by evaluating it
step-by-step.

tag[4]=false OR tag[4] IS NULL
NULL = false OR NULL IS NULL
NULL OR TRUE
NULL

Remember, "NULL = false" resolves to NULL, and "NULL OR TRUE" is also NULL.

> since my index can contain combinations of many tag[] columns it can
> look like this
> create index idx_test_2 on test_1(( ( ( NOT (tag[1]=false OR tag[1] IS
> NULL) ) AND ( NOT (tag[2]=false OR tag[2] IS NULL) ) ) AND NOT ( (
> NOT (tag[1]=false OR tag[1] IS NULL) ) AND ( NOT (tag[3]=false OR
> tag[3] IS NULL) ) ) ));

It'll help make it easier to maintain and more comprehensible if you
wrap that test up in an IMMUTABLE function. 'LANGUAGE SQL' is convenient
for this.

> which also doesnt get used by the select
> SELECT id FROM test WHERE ( ( NOT (tag[1]=false OR tag[1] IS NULL) )
> AND ( NOT (tag[2]=false OR tag[2] IS NULL) ) ) AND NOT ( ( NOT
> (tag[1]=false OR tag[1] IS NULL) ) AND ( NOT (tag[3]=false OR tag[3] IS
> NULL) ) );

and use the IMMUTABLE function in your tests.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/