IDS Forum

Re: Strange Optimization

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

In Response To: Strange Optimization (MICHAEL HOFFMAN)

Other ideas:

- Replace the join to the eau table with a derived table that has the

filtered results from eau. That will force a join of 0 rows (but still the

estimated join to 401 rows) which may convince the optimizer to reorder the

tables.

- Join eau to a derived table that results from joining the remaining

three tables.

All of these ideas will get you to examine many of the alternative queries
that will give you the desired results. One of these or some other form of
the query will be the best performer and it may NOT be the one you think it
should be. Remember Kagel's First Law of SQL:

Any SELECT statement can be rewritten many different ways and return the
same results. If you haven't tried them all you may not be using the best
performer!

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:50 PM, Art Kagel <art.kagel@gmail.com> wrote:

> 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.
>>
>>
>

--94eb2c1487be3f0160054e7d8134

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.