From: | Joseph S <jks(at)selectacast(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Index non-usage problem in 8.2.9 |
Date: | 2008-09-02 22:31:35 |
Message-ID: | g9kes6$60b$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2008-09-02 22:47:44 | Re: Subqueries in Check() -- Still Intentionally Omitted? |
Previous Message | Richard Broersma | 2008-09-02 22:30:58 | Subqueries in Check() -- Still Intentionally Omitted? |