Search Postgresql Archives

Re: Index non-usage problem in 8.2.9

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux