IDS Forum

Strange Optimization

Posted By: MICHAEL HOFFMAN
Date: Monday, 1 May 2017, at 5:16 p.m.

Hi All,
We haven't had any "Why is the Optimizer using this query path?" questions in a while, so I'll throw one in.
What caught me is that the optimizer is actually choosing to sequentially scan a linked table that is being aggregated -- when would that EVER be the table to drive from?
Maybe Art has an easy answer, but I sure don't see one.

Informix 12.1.FC4 running on Solaris 10

Here are the table stats:
hist_user: 42665 rows, 620 size, index on hu_id (serial); other columns: ua_id, uea_id, nm_id
user_ein_assoc: 40132 rows, 545 size, index on uea_id(serial), ua_id (integer), ein (varchar 10);
user_acct: 40130 rows, 219 size, index on ua_id (serial), nm_id (integer);
name_dtl: 1842706 rows, 2716 size, index on nm_id (serial), full_nm (BTS, varchar)

Here's the query and plan:
SELECT
ua.ua_id, ua.user_id, uea.ein, uea.role_nm, uea.firm_nm, uea.email,
MIN(hu.start_dtm) start_date,
n.first_nm, n.middle_nm, n.last_nm
FROM user_ein_assoc uea, user_acct ua,
hist_user hu,
name_dtl n
WHERE uea.ein = '66-9876152'
AND uea.record_status_cd = 'A'
AND uea.ua_id=ua.ua_id
AND ua.ua_id=hu.ua_id
AND n.nm_id=ua.nm_id
GROUP BY ua_id, user_id, ein, role_nm, firm_nm, email,

first_nm, middle_nm, last_nm
ORDER BY last_nm, first_nm
;

Estimated Cost: 42577
Estimated # of Rows Returned: 1024
Temporary Files Required For: Order By Group By

1) devdba.hu: SEQUENTIAL SCAN

2) devdba.ua: INDEX PATH

(1) Index Name: informix.pk_user_acct

Index Keys: ua_id (Serial, fragments: ALL)

Lower Index Filter: devdba.ua.ua_id = devdba.hu.ua_id
NESTED LOOP JOIN

3) devdba.uea: INDEX PATH

Filters: (devdba.uea.record_status_cd = 'A' AND devdba.uea.ein = '66-987
6152' )

(1) Index Name: informix.fk_ua_uae

Index Keys: ua_id (Serial, fragments: ALL)

Lower Index Filter: devdba.uea.ua_id = devdba.ua.ua_id
NESTED LOOP JOIN

4) devdba.n: INDEX PATH

(1) Index Name: informix.pk_name_dtl

Index Keys: nm_id (Serial, fragments: ALL)

Lower Index Filter: devdba.n.nm_id = devdba.ua.nm_id
NESTED LOOP JOIN

Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 hu
t2 ua
t3 uea
t4 n

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 42665 42665 42665 00:00.38 4892

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 42665 40129 42665 00:01.75 0

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 42665 74931 00:02.21 16229

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 0 401 6453698 02:00.61 0

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 1395 02:02.87 36280

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t4 0 1842690 0 00:00.00 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 1396 02:02.87 37377

type rows_prod est_rows rows_cons time est_cost
------------------------------------------------------------
group 0 1025 0 02:02.87 2968

type rows_sort est_rows rows_cons time est_cost
------------------------------------------------------------
sort 0 1025 0 02:02.87 648

We see that the slowest table isn't the Sequential Scan 'hu' table, but it's the 'uea' table. Interestingly enough, removing the 'hu' table (and associated aggregate) from the query, and the Optimizer correctly chooses to drive off the 'uea' table, and utilizes the index for ein:
1) devdba.uea: INDEX PATH

Filters: devdba.uea.record_status_cd = 'A'

(1) Index Name: informix.uea_ein_idx

Index Keys: ein (Serial, fragments: ALL)

Lower Index Filter: devdba.uea.ein = '66-9876152'

Update statistics has been run on all table.

Any thoughts on why the Optimizer is working this way, and how do fix the query to work efficiently?

Thanks,
Michael Hoffman

Messages In This Thread

• Strange Optimization
  MICHAEL HOFFMAN -- Monday, 1 May 2017, at 5:16 p.m.
  • Re: Strange Optimization
    Art Kagel -- Monday, 1 May 2017, at 5:51 p.m.
  • Re: Strange Optimization
    Art Kagel -- Monday, 1 May 2017, at 5:57 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.