Optimize Query for slow responce
26-Oct-1994
I can not understand the Informix Query Optimizer !
---------------------------------------------------
I have a fine large table and I create a query, where I select all fields from
the table. To lookup the records I have an unique index on a field called
'gd_kontonr' and as a second want, I have a filter 'gd_prod_kode < 90000'.
select * from ks_gdkonto
where gd_kontonr = "0003573508"
and gd_prod_kode < 90000;
This one takes loooooooong time to run....................
select * from ks_gdkonto
where gd_kontonr = "0003573508";
This one is very very fast, you can't start it before it is finished, but
I want to have 'gd_prod_kode < 90000' on the where part.
Now look at the Optimizer Query Plan:
QUERY: (LOW)
------
select * from ks_gdkonto
where gd_kontonr = "0003573508"
and gd_prod_kode < 90000
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) kundedb.ks_gdkonto: INDEX PATH
Filters: kundedb.ks_gdkonto.gd_kontonr = '0003573508'
(1) Index Keys: gd_prod_kode
Upper Index Filter: kundedb.ks_gdkonto.gd_prod_kode < 90000
It is totally upside down of what I expected !
This is the table create command:
create table "kundedb".ks_gdkonto
(
gd_seg_type char(3),
gd_kontonr char(10) not null,
gd_aftale_type char(1),
gd_prod_kode char(5),
gd_tankstorrel integer,
gd_tanktype char(2),
gd_naeste_gd integer,
gd_a_faktor smallfloat,
gd_b_faktor smallfloat,
gd_c_faktor smallfloat,
gd_fast_kfaktor char(1),
gd_akk_kvant float,
gd_akk_s_aar float,
gd_akk_f_aar float,
gd_opret_dato date,
gd_udgaaet date,
gd_rette_dato date,
gd_rette_tid datetime hour to second,
gd_rettet_af char(10),
gd_udg_kode char(1),
gd_2_kort char(1),
gd_2_dato date,
gd_4_opret date,
gd_4_lukket date
);
revoke all on "kundedb".ks_gdkonto from "public";
create unique index "kundedb".ks_gdk1 on "kundedb".ks_gdkonto (gd_kontonr);
create index "kundedb".ks_gdk2 on "kundedb".ks_gdkonto (gd_aftale_type);
create index "kundedb".ix454_4 on "kundedb".ks_gdkonto (gd_prod_kode);
create index "kundedb".ks_gdk4 on "kundedb".ks_gdkonto (gd_tanktype);
create index "kundedb".ks_gdk5 on "kundedb".ks_gdkonto (gd_fast_kfaktor);
Why can't the Optimizer use a better Query Plan on this simple select ?
Regards
-----------------------------------------------------------------
Databaseadministrator Poul Pedersen
Kuwait Petroleum (Danmark) A/S
Hummeltoftevej 49 Mail: pp@q8.dk
2830 Virum Voice: +45 45 98 45 94
Denmark Fax: +45 42 85 14 18
-----------------------------------------------------------------