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.
Sign up now.