Optimizer doesnt find the index on RSS
Hello,
When I run a SQL on a RSS instance which has a hint to use a particular index,
it fails to find that index, while on Primary, it finds the same indexe,
please see below
IDS version is 11.50.FC8 and OS is
uname -a
SunOS x4600-02 5.10 Generic_147441-07 i86pc i386 i86pc
RSS:
QUERY: (OPTIMIZATION TIMESTAMP: 05-14-2013 15:06:09) (FIRST_ROWS OPTIMIZATION)
------
SELECT {++ INDEX (t_quote_history tqh_idx1)} * FROM
t_quote_history WHERE qh_timestamp >= '2013-05-13 00:00:00'
AND qh_timestamp <= to_date('2013-05-13 16:00:00','%Y-%m-%d %H:%M:%S') +
interval (10) second to second
AND qh_publishtime_ts <= '2013-05-13 16:00:00'
and qh_update_type != 6
DIRECTIVES FOLLOWED:
DIRECTIVES NOT FOLLOWED:INDEX ( t_quote_history tqh_idx1 ) Invalid Index Name Specified.
Estimated Cost: 1461503
Estimated # of Rows Returned: 9862221
1) informix.t_quote_history: INDEX PATH
Filters: informix.t_quote_history.qh_publishtime_ts <= datetime(2013-05-13
16:00:00.000) year to fraction(3)
(1) Index Name: informix.tqh_idx7
Index Keys: qh_timestamp qh_nqbsecid qh_update_type (Key-First) (Serial,
fragments: ALL)
Lower Index Filter: informix.t_quote_history.qh_timestamp >=
datetime(2013-05-13 00:00:00.000) year to fraction(3)
Upper Index Filter: informix.t_quote_history.qh_timestamp <=
datetime(2013-05-13 16:00:10.00000) year to fraction(5)
Index Key Filters: (informix.t_quote_history.qh_update_type != 6 )
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t_quote_history
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 19 9862221 19 00:00.01 1461504
--------------------------------------------------------------------------------
------------------------------------------------------
PRIMARY NODE:
QUERY: (OPTIMIZATION TIMESTAMP: 05-14-2013 15:14:49) (FIRST_ROWS OPTIMIZATION)
------
SELECT {++ UNIQUE INDEX (t_quote_history tqh_idx1)} * FROM
t_quote_history WHERE qh_timestamp >= '2013-05-13 00:00:00'
AND qh_timestamp <= to_date('2013-05-13 16:00:00','%Y-%m-%d %H:%M:%S') +
interval (10) second to second
AND qh_publishtime_ts <= '2013-05-13 16:00:00'
and qh_update_type != 6
DIRECTIVES FOLLOWED:
INDEX ( t_quote_history tqh_idx1 )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 1733571
Estimated # of Rows Returned: 7534997
1) informix.t_quote_history: INDEX PATH
Filters: (informix.t_quote_history.qh_update_type != 6 AND
informix.t_quote_history.qh_publishtime_ts <= datetime(2013-05-13
16:00:00.000) year to fraction(3) )
(1) Index Name: informix.tqh_idx1
Index Keys: qh_timestamp qh_upd_seqnum (Serial, fragments: ALL)
Lower Index Filter: informix.t_quote_history.qh_timestamp >=
datetime(2013-05-13 00:00:00.000) year to fraction(3)
Upper Index Filter: informix.t_quote_history.qh_timestamp <=
datetime(2013-05-13 16:00:10.00000) year to fraction(5)
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t_quote_history
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 19 7534997 19 00:00.01 1733572
Does somebody know the reason?
regards,
Nitin