Thread: Index non-usage problem in 8.2.9
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.
Joseph S <jks@selectacast.net> writes:
> 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.
Works for me ...
regression=# create table d2(sgcode int, sacode int);
CREATE TABLE
regression=# create index d2i on d2 (sgcode, sacode) WHERE sacode IS NOT NULL AND sacode > 0;
CREATE INDEX
regression=# explain select count(*) from d2 where d2.sgcode = 156 AND d2.sacode IN(2,1);
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=8.54..8.55 rows=1 width=0)
-> Bitmap Heap Scan on d2 (cost=4.52..8.54 rows=1 width=0)
Recheck Cond: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
-> Bitmap Index Scan on d2i (cost=0.00..4.52 rows=1 width=0)
Index Cond: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
(5 rows)
You sure the server is 8.2.9? Awhile ago there were some bug fixes
around the handling of IS NULL/IS NOT NULL in predicates.
One thought is that the IS NOT NULL is really redundant, since it's
implied by the sacode > 0 test anyway. Does it work better if you
make the index just "WHERE sacode > 0" ?
regards, tom lane
Creating an index without the IS NOT NULL did not help. The complete
version:
PostgreSQL 8.2.9 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
Starting with a fresh database I got the same results you did, but not
with my production table.
Tom Lane wrote:
> Joseph S <jks@selectacast.net> writes:
>> 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.
>
> Works for me ...
>
> regression=# create table d2(sgcode int, sacode int);
> CREATE TABLE
> regression=# create index d2i on d2 (sgcode, sacode) WHERE sacode IS NOT NULL AND sacode > 0;
> CREATE INDEX
> regression=# explain select count(*) from d2 where d2.sgcode = 156 AND d2.sacode IN(2,1);
> QUERY PLAN
> ------------------------------------------------------------------------------------
> Aggregate (cost=8.54..8.55 rows=1 width=0)
> -> Bitmap Heap Scan on d2 (cost=4.52..8.54 rows=1 width=0)
> Recheck Cond: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
> -> Bitmap Index Scan on d2i (cost=0.00..4.52 rows=1 width=0)
> Index Cond: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
> (5 rows)
>
> You sure the server is 8.2.9? Awhile ago there were some bug fixes
> around the handling of IS NULL/IS NOT NULL in predicates.
>
> One thought is that the IS NOT NULL is really redundant, since it's
> implied by the sacode > 0 test anyway. Does it work better if you
> make the index just "WHERE sacode > 0" ?
>
> regards, tom lane
Tom Lane wrote: > Can you force it to use the partial index by dropping the other index? > (Use begin; drop index ...; explain ...; rollback; to avoid dropping > the index for real.) It's quite unclear at this point whether it I tried, and it ends up using a seqscan.
Joseph S <jks@selectacast.net> writes:
> Tom Lane wrote:
>> Can you force it to use the partial index by dropping the other index?
>> (Use begin; drop index ...; explain ...; rollback; to avoid dropping
>> the index for real.) It's quite unclear at this point whether it
> I tried, and it ends up using a seqscan.
Just to be sure, what if you set enable_seqscan = off?
If still not, then there must be something about the table or index
declaration that you didn't tell us. In the past, issues like use of
a domain instead of a bare datatype have been relevant ...
regards, tom lane
Actually sacode is an int2. I didn't mention it before because I
already tried explicit casts and that didn't do anything. Now I just
realized that in your testcase you use int instead of int2. I just retried:
[local]:playpen=# create table d2(sgcode int, sacode int2);
CREATE TABLE
Time: 13.748 ms
[local]:playpen=# create index d2i on d2 (sgcode, sacode) WHERE sacode
IS NOT NULL AND sacode > 0;
CREATE INDEX
Time: 30.734 ms
[local]:playpen=# explain select count(*) from d2 where d2.sgcode =
156 AND d2.sacode IN(2,1);
QUERY PLAN
--------------------------------------------------------------------------
Aggregate (cost=40.61..40.62 rows=1 width=0)
-> Seq Scan on d2 (cost=0.00..40.60 rows=1 width=0)
Filter: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::integer[])))
(3 rows)
[local]:playpen=# explain select count(*) from d2 where d2.sgcode =
156 AND d2.sacode IN(2::int2,1::int2);
QUERY PLAN
---------------------------------------------------------------------------
Aggregate (cost=40.61..40.62 rows=1 width=0)
-> Seq Scan on d2 (cost=0.00..40.60 rows=1 width=0)
Filter: ((sgcode = 156) AND (sacode = ANY ('{2,1}'::smallint[])))
(3 rows)
Time: 0.986 ms
Tom Lane wrote:
> Joseph S <jks@selectacast.net> writes:
>> Tom Lane wrote:
>>> Can you force it to use the partial index by dropping the other index?
>>> (Use begin; drop index ...; explain ...; rollback; to avoid dropping
>>> the index for real.) It's quite unclear at this point whether it
>
>> I tried, and it ends up using a seqscan.
>
> Just to be sure, what if you set enable_seqscan = off?
>
> If still not, then there must be something about the table or index
> declaration that you didn't tell us. In the past, issues like use of
> a domain instead of a bare datatype have been relevant ...
>
> regards, tom lane
Joseph S <jks@selectacast.net> writes:
> Starting with a fresh database I got the same results you did, but not
> with my production table.
So, what's different between your table declaration and my toy example?
Can you force it to use the partial index by dropping the other index?
(Use begin; drop index ...; explain ...; rollback; to avoid dropping
the index for real.) It's quite unclear at this point whether it
believes it *cannot* use the partial index, or whether it just thinks
the other index is cheaper.
regards, tom lane
Joseph S <jks@selectacast.net> writes:
> Actually sacode is an int2.
Ah. 8.2 is not very good at proving cross-type predicate conditions,
because it lacks the concept of an operator family. You need to declare
the index this way:
create index d2i on d2 (sgcode, sacode)
WHERE sacode IS NOT NULL AND sacode > 0::int2;
(As previously noted, you don't really need the IS NOT NULL part of the
condition, but that isn't what's causing the problem here.)
regards, tom lane