IDS Forum

Re: Strange Optimization

Posted By: Art Kagel
Date: Monday, 1 May 2017, at 5:51 p.m.

In Response To: Strange Optimization (MICHAEL HOFFMAN)

Michael:

OK, I CAN tell you why the optimizer chose the path it chose and there are
several reasons that all add up:

- There are no indexes on the uea table on the filter columns ein and

record_status_cd. The probably MEDIUM mode distributions on those columns

have led the optimizer to guess that there are only about 401 rows that

match these combined filters. A sequential scan on a 42000 row table to

filter out over 99% of the rows when only 3 rows fit on a page is too

costly.

- The nested loop join on uea, however, is adding up to 6 IOs per row

compared and since the number of rows in eau and in the joined results of

ua and hu have nearly the same count it looks like the engine had to join

42000+ rows using index lookups which is why it took a bit over 2 minutes

to complete that second join.

- Eliminating hu from the query changes the whole game. The initial join

has to scan either eau or ua which again have about the same number of rows

so basically a toss up ignoring the filters. However by scanning eau and

joining to eu the optimizer knows that it will only have to join about 401

times versus 42000 joins if it scans eu. No brainer. Comparing this back to

the original query is an apples to shoes comparison. You can't do that and

gain any understanding of what happened. Best to play with the original

query if you want to understand.

What to do:

1. You could add an ORDERED optimizer directive to the original query so

that it will scan the eau table first (since it is listed first in the FROM

clause).

2. You can try reducing the cost that the optimizer places on index

lookups by setting OPT_SEEK_FACTOR to a lower value in the ONCONFIG file

(the default is 6 if it is not present) which will cause the optimizer to

behave more like the v11.50 optimizer did (earlier optimizer releases did

not take index lookup cost into consideration - so setting it to zero

reverts the optimizer to 11.50 levels).

3. You could add singleton indexes on the two filter columns and enable

multi-index scanning so the engine will start with an indexed scan of eau.

4. Improve the quality of the data distributions on the filter columns

to HIGH or MEDIUM with a higher SAMPLING SIZE or RESOLUTION or both.

Art

Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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, May 1, 2017 at 5:16 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote:

> 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
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--f403045d172a09d4b1054e7d6be4

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.