PG 8.0.1 Does not use Index with IS NOT NULL - Mailing list pgsql-general

Hy List,

I have a problem with this Query :
SELECT * FROM lifsch WHERE l_dokunr IS NOT NULL ORDER BY l_dokunr;

CIMSOFT=# ANALYSE lifsch;
ANALYZE

CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM lifsch WHERE l_dokunr IS NULL;
                                                       QUERY PLAN

--------------------------------------------------------------------------------
  Index Scan using test on lifsch  (cost=0.00..1400.20 rows=372
width=201) (actual time=0.000..0.000 rows=189 loops=1)
    Filter: (l_dokunr IS NULL)
  Total runtime: 0.000 ms
(3 rows)

ok, thats fine


CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM lifsch WHERE l_dokunr IS NOT NULL;
                                                   QUERY PLAN

--------------------------------------------------------------------------------
  Seq Scan on lifsch  (cost=0.00..4469.47 rows=39476 width=201) (actual
time=0.000..360.000 rows=40652 loops=1)
    Filter: (l_dokunr IS NOT NULL)
  Total runtime: 510.000 ms
(3 rows)

not fine but 510ms is ok. (why does the planner do not use the index
"lifsch_dokunr" btree (l_dokunr) WHERE l_dokunr IS NOT NULL)???


CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM lifsch WHERE l_dokunr IS NOT
NULL ORDER BY l_dokunr;
                                                   QUERY PLAN

--------------------------------------------------------------------------------
  Sort  (cost=11768.20..11866.89 rows=39476 width=201) (actual
time=4837.000..4997.000 rows=40652 loops=1)
    Sort Key: l_dokunr
    ->  Seq Scan on lifsch  (cost=0.00..4469.47 rows=39476 width=201)
(actual time=0.000..1350.000 rows=40652 loops=1)
          Filter: (l_dokunr IS NOT NULL)
  Total runtime: 5107.000 ms
(5 rows)

5000ms, thats not fine! ORDER BY seems to slow down everything.

CIMSOFT=# SELECT count(*) FROM lifsch ;
  count
-------
  40841
(1 row)

CIMSOFT=# SELECT count(*) FROM lifsch WHERE l_dokunr IS NULL;
  count
-------
    189
(1 row)


Table definition:

CIMSOFT=# \d lifsch
                                      Table "public.lifsch"
    Column   |         Type          |                        Modifiers

------------+-----------------------+-------------------------------------------
---------------
  l_nr       | integer               | not null default
nextval('public.lifsch_l_
nr_seq'::text)
  l_krz      | character varying(9)  | not null
  l_krzl     | character varying(9)  | not null
  l_krzf     | character varying(9)  | not null
  l_aknr     | character varying(40) | not null
  l_ag_id    | integer               |
  l_ldat     | date                  | not null default currenttime()
  l_lgort    | character varying(50) | not null default ''::character
varying
  l_lgchnr   | character varying(50) | not null default ''::character
varying
  l_abg_mec  | integer               | not null
  l_abgg     | real                  | not null
  l_abgg_uf1 | real                  |
  l_vkp_uf1  | real                  |
  l_vkpbas   | real                  |
  l_vkp      | real                  |
  l_arab     | real                  |
  l_def      | boolean               |
  l_azutx    | text                  |
  l_gew      | real                  |
  l_versart  | character varying(30) |
  l_dokunr   | integer               |
  l_bz_bnr   | integer               |
  l_dim1     | real                  | not null default 0
  l_dim2     | real                  | not null default 0
  l_dim3     | real                  | not null default 0
  dbrid      | character varying     | default nextval('db_id_seq'::text)
Indexes:
     "lifsch_pkey" PRIMARY KEY, btree (l_nr)
     "lifsch_idindex" UNIQUE, btree (dbrid)
     "lifsch_dokunr" btree (l_dokunr) WHERE l_dokunr IS NOT NULL
     "lifsch_ldat" btree (l_ldat)
     "lifsch_seldoku" btree (l_krzl, l_dokunr)
     "test" btree (l_dokunr) WHERE l_dokunr IS NULL