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