hello

i am seeing more queries in version 11 that use the auto index feature
than i ever recall seeing in version 10. it seems like the optimizer
behaves differently but have not seen much in the literature about it.
what is strange is that it is auto indexing on tables that are small
(anywhere from 50 rows to a couple thousand) and also it is indexing
fields like active, where there are only two possible values. any
insight?
thanks

On Mon, Oct 11, 2010 at 9:24 PM, Tom Lehr <tomcaml (AT) gmail (DOT) com> wrote:

Personaly I haven't noticed it... I've probably seen AUTOINDEX a couple of
times and it's possible that one was on 11.5.
If you feel it's choosing badly, it would be nice if you could open a PMR
and/or give us a few examples.
I always felt that AUTOINDEX was a "last resource" kind of query plan. Maybe
it means your tables would benefit from some more indexes...
But I don't have any more info on this than a normal user...
Also, the version would be helpful...

Regards.



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Hmm, now that Tom mentioned it... I've seen AUTOINDEX 3 or 4 times this year
on different queries at two different client sites and before that I don't
think I've seen even one in the last 8 or 10 years. Tom may be on to
something. Now in defense of 11.50, in all of those queries, there was a
"missing" index that improved the query. Just because 11.50 is doing things
differently than 11.10, 10.00, 9.xx, or 7.xx doesn't mean it's not doing a
better job of it.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Mon, Oct 11, 2010 at 5:04 PM, Fernando Nunes <domusonline (AT) gmail (DOT) com>wrote:

We are on 11.5.FC8
AIX 5.3
HDR pair

The SQL is generated from Hibernate and it involves many left outer
joins
so not sure if that "throws" thing off but it even auto indexes tables
that have no rows.
So not making any opinions on bad or good but it does seem surprising
- one sql has 7 auto indexes on it and another has 5.
I think I will open a PMR since I would like to know more.

Overall the move to 11 has been very good for us except for open PMR's
involving crashes happening on the Secondary, which we are pursuing
for answers as we are not sure if FC8 will have the fix for our
issue(s) with the crashes.

For fun, here is the SQL and explain:

-- execution plan at /home/informix/sqexplain.out at
tmsdb01.werner.com
QUERY: (OPTIMIZATION TIMESTAMP: 10-12-2010 08:43:54)
------
SELECT inboundshi0_.ib_shipment_status_id AS ib1_799_9_,
inboundshi0_.active AS
active799_9_, inboundshi0_.create_date AS create3_799_9_,
inboundshi0_.created_by AS created4_799_9_,
inboundshi0_.update_date AS
update5_799_9_, inboundshi0_.update_version AS update6_799_9_,
inboundshi0_.updated_by AS updated7_799_9_,
inboundshi0_.activity_type
AS activity8_799_9_, inboundshi0_.carrier_scac AS
carrier9_799_9_,
inboundshi0_.commodity_cube AS commodity10_799_9_,
inboundshi0_.commodity_cube_uom_id AS commodity11_799_9_,
inboundshi0_.commodity_pallet_count AS commodity12_799_9_,
inboundshi0_.commodity_quantity AS commodity13_799_9_,
inboundshi0_.commodity_quantity_uom_id AS commodity14_799_9_,
inboundshi0_.commodity_weight AS commodity15_799_9_,
inboundshi0_.commodity_weight_uom_id AS commodity16_799_9_,
inboundshi0_.control_role_id AS control17_799_9_,
inboundshi0_.edi_doc_control_number AS edi18_799_9_,
inboundshi0_.edi_id
AS edi19_799_9_, inboundshi0_.ib_shipment_status_type_id AS
ib43_799_9_,
inboundshi0_.late_code_edi_code AS late20_799_9_,
inboundshi0_.latitude
AS latitude799_9_, inboundshi0_.load_id AS load22_799_9_,
inboundshi0_.longitude AS longitude799_9_, inboundshi0_.po_id
AS
po24_799_9_, inboundshi0_.po_number AS po25_799_9_,
inboundshi0_.po_quantity AS po26_799_9_,
inboundshi0_.po_unit_code AS
po27_799_9_, inboundshi0_.po_weight AS po28_799_9_,
inboundshi0_.po_weight_type AS po29_799_9_,
inboundshi0_.position_history_datetime AS position30_799_9_,
inboundshi0_.position_history_location_desc AS
position31_799_9_,
inboundshi0_.processing_status_id AS processing44_799_9_,
inboundshi0_.project_role_id AS project32_799_9_,
inboundshi0_.sender_scac AS sender33_799_9_,
inboundshi0_.staging_purpose_id AS staging34_799_9_,
inboundshi0_.stop_city AS stop35_799_9_,
inboundshi0_.stop_seq_number AS
stop36_799_9_, inboundshi0_.stop_state AS stop37_799_9_,
inboundshi0_.tractor_number AS tractor38_799_9_,
inboundshi0_.tractor_sub_type AS tractor39_799_9_,
inboundshi0_.trailer_number AS trailer40_799_9_,
inboundshi0_.trailer_sub_type AS trailer41_799_9_,
inboundshi0_.transaction_number AS transac42_799_9_,
activitya1_.ib_shipment_status_id AS ib10_11_,
activitya1_.ib_shipment_status_activity_attribute_ id AS
ib1_11_,
activitya1_.ib_shipment_status_activity_attribute_ id AS
ib1_835_0_,
activitya1_.active AS active835_0_, activitya1_.create_date AS
create3_835_0_, activitya1_.created_by AS created4_835_0_,
activitya1_.update_date AS update5_835_0_,
activitya1_.update_version AS
update6_835_0_, activitya1_.updated_by AS updated7_835_0_,
activitya1_.attribute_type_id AS attribute8_835_0_,
activitya1_.ib_shipment_status_id AS ib10_835_0_,
activitya1_.value AS
value835_0_, activityc2_.ib_shipment_status_id AS ib10_12_,
activityc2_.ib_shipment_status_activity_comment_id AS ib1_12_,
activityc2_.ib_shipment_status_activity_comment_id AS
ib1_753_1_,
activityc2_.active AS active753_1_, activityc2_.create_date AS
create3_753_1_, activityc2_.created_by AS created4_753_1_,
activityc2_.update_date AS update5_753_1_,
activityc2_.update_version AS
update6_753_1_, activityc2_.updated_by AS updated7_753_1_,
activityc2_.comment_type_id AS comment8_753_1_,
activityc2_.ib_shipment_status_id AS ib10_753_1_,
activityc2_.value AS
value753_1_, activitye3_.ib_shipment_status_id AS ib10_13_,
activitye3_.ib_shipment_status_activity_event_id AS ib1_13_,
activitye3_.ib_shipment_status_activity_event_id AS
ib1_740_2_,
activitye3_.active AS active740_2_, activitye3_.create_date AS
create3_740_2_, activitye3_.created_by AS created4_740_2_,
activitye3_.update_date AS update5_740_2_,
activitye3_.update_version AS
update6_740_2_, activitye3_.updated_by AS updated7_740_2_,
activitye3_.event_type_id AS event8_740_2_,
activitye3_.ib_shipment_status_id AS ib10_740_2_,
activitye3_.value AS
value740_2_, activityr4_.ib_shipment_status_id AS ib10_14_,
activityr4_.ib_shipment_status_activity_reference_ id AS
ib1_14_,
activityr4_.ib_shipment_status_activity_reference_ id AS
ib1_748_3_,
activityr4_.active AS active748_3_, activityr4_.create_date AS
create3_748_3_, activityr4_.created_by AS created4_748_3_,
activityr4_.update_date AS update5_748_3_,
activityr4_.update_version AS
update6_748_3_, activityr4_.updated_by AS updated7_748_3_,
activityr4_.ib_shipment_status_id AS ib10_748_3_,
activityr4_.reference_type_id AS reference8_748_3_,
activityr4_.value AS
value748_3_, shipmentst5_.shipment_status_type_id AS
shipment1_745_4_,
shipmentst5_.active AS active745_4_, shipmentst5_.create_date
AS
create3_745_4_, shipmentst5_.created_by AS created4_745_4_,
shipmentst5_.update_date AS update5_745_4_,
shipmentst5_.update_version
AS update6_745_4_, shipmentst5_.updated_by AS updated7_745_4_,
shipmentst5_.description_tx AS descript8_745_4_,
shipmentst5_.java_name
AS java9_745_4_, processing6_.processing_status_id AS
processing1_833_5_,
processing6_.active AS active833_5_, processing6_.create_date
AS
create3_833_5_, processing6_.created_by AS created4_833_5_,
processing6_.update_date AS update5_833_5_,
processing6_.update_version
AS update6_833_5_, processing6_.updated_by AS updated7_833_5_,
processing6_.description_tx AS descript8_833_5_,
processing6_.edi_code
AS edi9_833_5_, shipments7_.ib_shipment_status_id AS ib11_15_,
shipments7_.ib_shipment_status_error_id AS ib1_15_,
shipments7_.ib_shipment_status_error_id AS ib1_594_6_,
shipments7_.active AS active594_6_, shipments7_.create_date AS
create3_594_6_, shipments7_.created_by AS created4_594_6_,
shipments7_.update_date AS update5_594_6_,
shipments7_.update_version AS
update6_594_6_, shipments7_.updated_by AS updated7_594_6_,
shipments7_.error_detail_tx AS error8_594_6_,
shipments7_.error_message_tx AS error9_594_6_,
shipments7_.error_status_id AS error10_594_6_,
shipments7_.ib_shipment_status_id AS ib11_594_6_,
tourattri8_.ib_shipment_status_id AS ib10_16_,
tourattri8_.ib_shipment_status_tour_attribute_id AS ib1_16_,
tourattri8_.ib_shipment_status_tour_attribute_id AS
ib1_786_7_,
tourattri8_.active AS active786_7_, tourattri8_.create_date AS
create3_786_7_, tourattri8_.created_by AS created4_786_7_,
tourattri8_.update_date AS update5_786_7_,
tourattri8_.update_version AS
update6_786_7_, tourattri8_.updated_by AS updated7_786_7_,
tourattri8_.attribute_type_id AS attribute8_786_7_,
tourattri8_.ib_shipment_status_id AS ib10_786_7_,
tourattri8_.value AS
value786_7_, tourrefer9_.ib_shipment_status_id AS ib10_17_,
tourrefer9_.ib_shipment_status_tour_reference_id AS ib1_17_,
tourrefer9_.ib_shipment_status_tour_reference_id AS
ib1_828_8_,
tourrefer9_.active AS active828_8_, tourrefer9_.create_date AS
create3_828_8_, tourrefer9_.created_by AS created4_828_8_,
tourrefer9_.update_date AS update5_828_8_,
tourrefer9_.update_version AS
update6_828_8_, tourrefer9_.updated_by AS updated7_828_8_,
tourrefer9_.ib_shipment_status_id AS ib10_828_8_,
tourrefer9_.reference_type_id AS reference8_828_8_,
tourrefer9_.value AS
value828_8_
FROM ib_shipment_status inboundshi0_ LEFT OUTER JOIN
ib_shipment_status_activity_attribute activitya1_ ON

inboundshi0_.ib_shipment_status_id=activitya1_.ib_ shipment_status_id
AND(activitya1_.active=1) LEFT OUTER JOIN
ib_shipment_status_activity_comment
activityc2_ ON

inboundshi0_.ib_shipment_status_id=activityc2_.ib_ shipment_status_id
AND(activityc2_.active=1) LEFT OUTER JOIN
ib_shipment_status_activity_event
activitye3_ ON

inboundshi0_.ib_shipment_status_id=activitye3_.ib_ shipment_status_id
AND(activitye3_.active=1) LEFT OUTER JOIN
ib_shipment_status_activity_reference
activityr4_ ON

inboundshi0_.ib_shipment_status_id=activityr4_.ib_ shipment_status_id
AND(activityr4_.active=1) LEFT OUTER JOIN shipment_status_type
shipmentst5_ ON

inboundshi0_.ib_shipment_status_type_id=shipmentst 5_.shipment_status_type_id
LEFT OUTER JOIN processing_status processing6_ ON

inboundshi0_.processing_status_id=processing6_.pro cessing_status_id
LEFT
OUTER JOIN ib_shipment_status_error shipments7_ ON

inboundshi0_.ib_shipment_status_id=shipments7_.ib_ shipment_status_id
AND(shipments7_.active=1) LEFT OUTER JOIN
ib_shipment_status_tour_attribute
tourattri8_ ON

inboundshi0_.ib_shipment_status_id=tourattri8_.ib_ shipment_status_id
AND(tourattri8_.active=1) LEFT OUTER JOIN
ib_shipment_status_tour_reference
tourrefer9_ ON

inboundshi0_.ib_shipment_status_id=tourrefer9_.ib_ shipment_status_id
AND(tourrefer9_.active=1)
WHERE inboundshi0_.ib_shipment_status_id=1000

Estimated Cost: 5534
Estimated # of Rows Returned: 73

1) informix.inboundshi0_: INDEX
PATH

(1) Index Name:
informix.pk_id_ib_shipment_status
Index Keys:
ib_shipment_status_id (Serial, fragments: ALL)
Lower Index Filter:
informix.inboundshi0_.ib_shipment_status_id = 1000

2) informix.shipments7_:
AUTOINDEX PATH

Filters:
Table Scan Filters:
informix.shipments7_.active = 1

(1) Index Name: (Auto Index)
Index Keys:
ib_shipment_status_id
Lower Index Filter:
informix.inboundshi0_.ib_shipment_status_id =
informix.shipments7_.ib_shipment_status_id

ON-Filters:
(informix.inboundshi0_.ib_shipment_status_id =
informix.shipments7_.ib_shipment_status_id AND
informix.shipments7_.active = 1 )
NESTED LOOP JOIN(LEFT OUTER JOIN)

3) informix.shipmentst5_: INDEX PATH

(1) Index Name: informix. 103_47
Index Keys:
shipment_status_type_id (Serial, fragments: ALL)
Lower Index Filter:
informix.inboundshi0_.ib_shipment_status_type_id =
informix.shipmentst5_.shipment_status_type_id

ON-
Filters:informix.inboundshi0_.ib_shipment_status_t ype_id =
informix.shipmentst5_.shipment_status_type_id
NESTED LOOP JOIN(LEFT OUTER JOIN)

4) informix.processing6_: INDEX PATH

(1) Index Name: informix. 120_269
Index Keys: processing_status_id
(Serial, fragments: ALL)
Lower Index Filter:
informix.inboundshi0_.processing_status_id =
informix.processing6_.processing_status_id

ON-
Filters:informix.inboundshi0_.processing_status_id =
informix.processing6_.processing_status_id
NESTED LOOP JOIN(LEFT OUTER JOIN)

5) informix.activityr4_: AUTOINDEX PATH

Filters:
Table Scan Filters:
informix.activityr4_.active = 1

(1) Index Name: (Auto Index)
Index Keys: ib_shipment_status_id
Lower Index Filter:
informix.inboundshi0_.ib_shipment_status_id =
informix.activityr4_.ib_shipment_status_id

ON-Filters:
(informix.inboundshi0_.ib_shipment_status_id =
informix.activityr4_.ib_shipment_status_id AND
informix.activityr4_.active = 1 )
NESTED LOOP JOIN(LEFT OUTER JOIN)

6) informix.tourattri8_: AUTOINDEX PATH

Filters:
Table Scan Filters:
informix.tourattri8_.active = 1

(1) Index Name: (Auto Index)
Index Keys: ib_shipment_status_id
Lower Index Filter:
informix.inboundshi0_.ib_shipment_status_id =
informix.tourattri8_.ib_shipment_status_id

ON-Filters:
(informix.inboundshi0_.ib_shipment_status_id =
informix.tourattri8_.ib_shipment_status_id AND
informix.tourattri8_.active = 1 )
NESTED LOOP JOIN(LEFT OUTER JOIN)

7) informix.activityc2_: AUTOINDEX PATH

Filters:
Table Scan Filters:
informix.activityc2_.active = 1

(1) Index Name: (Auto Index)
Index Keys: ib_shipment_status_id
Lower Index Filter:
informix.inboundshi0_.ib_shipment_status_id =
informix.activityc2_.ib_shipment_status_id

ON-Filters:
(informix.inboundshi0_.ib_shipment_status_id =
informix.activityc2_.ib_shipment_status_id AND
informix.activityc2_.active = 1 )
NESTED LOOP JOIN(LEFT OUTER JOIN)

8) informix.activitya1_: AUTOINDEX PATH

Filters:
Table Scan Filters: informix.activitya1_.active =
1

(1) Index Name: (Auto Index)
Index Keys: ib_shipment_status_id
Lower Index Filter:
informix.inboundshi0_.ib_shipment_status_id =
informix.activitya1_.ib_shipment_status_id

ON-Filtersinformix.inboundshi0_.ib_shipment_status_ id =
informix.activitya1_.ib_shipment_status_id AND
informix.activitya1_.active = 1 )
NESTED LOOP JOIN(LEFT OUTER JOIN)

9) informix.activitye3_: AUTOINDEX PATH

Filters:
Table Scan Filters: informix.activitye3_.active = 1

(1) Index Name: (Auto Index)
Index Keys: ib_shipment_status_id
Lower Index Filter:
informix.inboundshi0_.ib_shipment_status_id =
informix.activitye3_.ib_shipment_status_id

ON-Filtersinformix.inboundshi0_.ib_shipment_status_ id =
informix.activitye3_.ib_shipment_status_id AND
informix.activitye3_.active = 1 )
NESTED LOOP JOIN(LEFT OUTER JOIN)

10) informix.tourrefer9_: AUTOINDEX PATH

Filters:
Table Scan Filters: informix.tourrefer9_.active = 1

(1) Index Name: (Auto Index)
Index Keys: ib_shipment_status_id
Lower Index Filter:
informix.inboundshi0_.ib_shipment_status_id =
informix.tourrefer9_.ib_shipment_status_id

ON-Filtersinformix.inboundshi0_.ib_shipment_status_ id =
informix.tourrefer9_.ib_shipment_status_id AND
informix.tourrefer9_.active = 1 )
NESTED LOOP JOIN(LEFT OUTER JOIN)