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

> Date: Wed, 21 Jul 2010 12:38:55 +0800
> From: craig(at)postnewspapers(dot)com(dot)au
> To: prometheus__0(at)hotmail(dot)com
> CC: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] what do i need to know about array index?
>
> On 20/07/10 18:27, Prometheus Prometheus wrote:
>
> What's with the pseudonym?
nothing special, just an account from my youth ^^
>
> > 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';

nice, i didnt know this one
and it works, the index is used

to give others a hint on this
i created 2 indexes where the first one didnt seem to work
the second worked
to know if the first one is ok i disable sequential scan's and queried the db again
now the index was used
so it seems the planner thinks a seqscan works better which may change in future as more rows are added/changed

set enable_seqscan=off;
explain select * from test where tag[4] IS DISTINCT FROM true;

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

head -> wall
thx for the hint

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

hmm, i see what you mean
ill have to think about how i can get this into my design
anyway thx again

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

kind regards

>
> --
> Craig Ringer
>
> Tech-related writing: http://soapyfrogs.blogspot.com/

_________________________________________________________________
Hotmail: Free, trusted and rich email service.
https://signup.live.com/signup.aspx?id=60969