Re: [PERFORM] will the planner ever use an index when the condition is <> ?

2011-12-18 Thread Roxanne Reid-Bennett
On 12/18/2011 1:31 PM, Tom Lane wrote: If you have a specific case where that's not true, you might consider a partial index (CREATE INDEX ... WHERE x <> constant). But the details of that would depend a lot on the queries you're concerned about. regards, tom lane Which I had tried in the fo

Re: [PERFORM] will the planner ever use an index when the condition is <> ?

2011-12-18 Thread Marti Raudsepp
On Sun, Dec 18, 2011 at 16:52, Roxanne Reid-Bennett wrote: > Is there an index type that can check "not equal"? > This specific column has a limited number of possible values - it is > essentially an enumerated list. Instead of writing WHERE foo<>3 you could rewrite it as WHERE foo IN (1,2,4,...)

Re: [PERFORM] will the planner ever use an index when the condition is <> ?

2011-12-18 Thread Tom Lane
Roxanne Reid-Bennett writes: > On 12/17/2011 11:24 AM, Filip Rembiałkowski wrote: >> Normally there is no chance it could work, >> because (a) the planner does not know all possible values of a column, >> and (b) btree indexes cannot search on "not equal" operator. > Is there an index type that

Re: [PERFORM] will the planner ever use an index when the condition is <> ?

2011-12-18 Thread Roxanne Reid-Bennett
On 12/17/2011 11:24 AM, Filip Rembiałkowski wrote: Normally there is no chance it could work, because (a) the planner does not know all possible values of a column, and (b) btree indexes cannot search on "not equal" operator. Is there an index type that can check "not equal"? This specific colu

Re: [PERFORM] will the planner ever use an index when the condition is <> ?

2011-12-18 Thread Віталій Тимчишин
17.12.2011 18:25 пользователь "Filip Rembiałkowski" написал: > > Normally there is no chance it could work, > because (a) the planner does not know all possible values of a column, > and (b) btree indexes cannot search on "not equal" operator. > Why so? a<>b is same as (ab), so, planner should ch

Re: [PERFORM] will the planner ever use an index when the condition is <> ?

2011-12-17 Thread Filip Rembiałkowski
Normally there is no chance it could work, because (a) the planner does not know all possible values of a column, and (b) btree indexes cannot search on "not equal" operator. BTW I've just made a case where - logically - it could work, but it still does not: create table nums ( num int4 not null

[PERFORM] will the planner ever use an index when the condition is <> ?

2011-12-17 Thread Roxanne Reid-Bennett
I have a query that used <> against an indexed column. In this case I can use the reverse and use in or = and get the performance I need... but "in general"... will the planner ever use an index when the related column is compared using <>? I feel like the answer is no, but wanted to ask. Roxa