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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
