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/