what do i need to know about array index?

From: Prometheus Prometheus <prometheus__0(at)hotmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: what do i need to know about array index?
Date: 2010-07-20 10:27:00
Message-ID: SNT126-W27DB2D48FEF86786A9501AE5A00@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


hi

i have a table like this
CREATE TABLE test(
id bigint,
tag boolean[]
) WITH ( OIDS=FALSE );

this is a parent table of some partition tables
one table contains 1mio entries
well in the future it should - currently only filled with test data

the array is used to 'tag' a row
which means every column of the array has some meaning which can be turned on and off
the tag's topic is created by a user and using some php scripts
the row's are tagged (turned on and off)

now i want to query e.g. table test_1 which has 1 mio entries
one problem i have to mention is a NULL value
since an entry can have 3 values t,f,null but my application expects only 2 values i modified my queries so that
tag[n]=true equals NOT (tag[n]=false OR tag[n] IS NULL)
the problem was that complex queries just ignored NULL values instead of counting as false - which makes sense

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

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

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

vacuum analyze after create index has been done and im using 8.4

o) is there some obvious bug in my usage so the index can't be used? what im missing here?
o) how does the planer know that a seq scan is faster? since im not sure if its just a feature which is not available (like is null index in 8.2) or im doing something wrong im a bit stuck here
o) and for those who have a great idea or suggestion for alternative solutions, please tell me

thx

_________________________________________________________________
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
https://signup.live.com/signup.aspx?id=60969

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oleg Bartunov 2010-07-20 11:00:22 Re: Full Text Search ideas
Previous Message Rikard Bosnjakovic 2010-07-20 10:14:34 Re: New DB-design - help and documentation pointers appreciated