|
> Date: Wed, 21 Jul 2010 12:38:55 +0800 > From: craig@xxxxxxxxxxxxxxxxxxxxx > To: prometheus__0@xxxxxxxxxxx > CC: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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. Get it now. |
