Index non-usage problem in 8.2.9
От | Joseph S |
---|---|
Тема | Index non-usage problem in 8.2.9 |
Дата | |
Msg-id | g9kes6$60b$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: Index non-usage problem in 8.2.9
|
Список | pgsql-general |
I have a table, d2, that has a field sacode that is almost always null. In fact the stanullfrac in pg_statistic for this column is 1. I have this index on my table: "d2_sgcode_sacode_idx" btree (sgcode, sacode) WHERE sacode IS NOT NULL AND sacode > 0 The first version of my query wasn't using that index for some reason: p10:owl=# explain select count(*) from d2 where d2.sgcode = 156 AND d2.sacode IN(2,1); QUERY PLAN ------------------------------------------------------------------------------------------------------------- Aggregate (cost=208074.99..208075.00 rows=1 width=0) -> Bitmap Heap Scan on d2 (cost=175091.29..208074.99 rows=1 width=0) Recheck Cond: (sgcode = 156) Filter: (sacode = ANY ('{2,1}'::integer[])) -> Bitmap Index Scan on d2_lower_username_sgcode_key (cost=0.00..175091.29 rows=9431 width=0) Index Cond: (sgcode = 156) (6 rows) Time: 0.531 ms I accidentally stumbled upon the solution: p10:owl=# explain select count(*) from d2 where d2.sgcode = 156 AND d2.sacode IN(2,1) and d2.sacode > 0; QUERY PLAN ---------------------------------------------------------------------------------------------------- Aggregate (cost=16.33..16.34 rows=1 width=0) -> Index Scan using d2_sgcode_sacode_idx on d2 (cost=0.00..16.33 rows=1 width=0) Index Cond: ((sgcode = 156) AND (sacode > 0)) Filter: (sacode = ANY ('{2,1}'::integer[])) (4 rows) Time: 0.710 ms It seems that postgres can't figure out that it can use the index on sacode unless I put "d2.sacode > 0" in my where clause. It won't use the index if I use "d2.sacode >= 1", for example.
В списке pgsql-general по дате отправления: