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
