Index non-usage problem in 8.2.9

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.

Responses

Browse pgsql-general by date

  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?