Thread: getting an index to work with partial indices ...
Try as I might, I can't seem to get it to work ... table has >9million rows in it, I've created an index "using btree ( priority ) where priority < 0;", where the table distribution looks like: priority | count ----------+--------- -2 | 138435 -1 | 943250 1 | 3416 9 | 1134171 | 7276960 (5 rows) And it still won't use the index: # explain update table set priority = -3 where priority = -1; QUERY PLAN ------------------------------------------------------------------ Seq Scan on table (cost=0.00..400735.90 rows=993939 width=278) Filter: (priority = -1) (2 rows) But, ti will if I try 'priority = -2' ... what is teh threshhold for using the index? obviously 10% of the records is too high ... thanks ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes: > But, ti will if I try 'priority = -2' ... what is teh threshhold for using > the index? obviously 10% of the records is too high ... Depends on a lot of factors, but usually somewhere between 1% and 10%. (The new bitmap index scan code in 8.1 should be workable for higher percentages.) If this doesn't seem to square with reality for you, you might try reducing random_page_cost. regards, tom lane