Subject: Query performance issue

From: yudhi s <[email-masked]>
Date: Wed, 16 Oct 2024 23:20:36 +0530

Hi,

Below question got in one discussion.Appreciate any guidance on this.

Below is a query which is running for ~40 seconds. As it's a query which is
executed from UI , we were expecting it to finish in <~5 seconds. It has a
"IN" and a "NOT IN" subquery , from the execution path it seems the total
response time is mainly, to be sum of the "IN" and the "NOT IN" subquery
section. My thought was that both "IN" and "NOT IN" should be
executed/evaluated in parallel but not in serial fashion.

In the execution path below , the line number marked in *bold* are the top
lines for the IN and NOT IN subquery evaluation and they are showing
"Actual time" as  Approx ~9 seconds and ~8 seconds and they seems to be
summed up and the top lines showing it to be ~19 seconds. Then onwards it
keeps on increasing with other "nested loop" joins.

*Note*:- This query is running on a MYSQL 8.0 database. So I'm wondering if
there is any mysql list similar to Oracle list , in which i can share this
issue?

Added the query in below path:-

https://gist.github.com/databasetech0073/95bce00c3a6bd4ae8d195401e0383185

SELECT ......
FROM R_CON_ESTS RC_STS,
     R_CON rc,
     D_LKP D_LKP_STS,
D_LKP D_LKP_FRQ,
 (select RCE.DRV_DT, RCE.AID, RCE.R_CON_ID, RCE.R_CON_VER_NB
from R_CON_E RCE
where RCE.MTNE_ID in (SELECT  MI1.MTNE_ID
  FROM M_INF mi1 WHERE MI1.AID = :AID
  UNION
  SELECT rg.RG_MF_SK_ID
 from RG_M_F_INF rg where rg.AS_ID =:AID
  UNION
  SELECT fti.FT_SRK_ID
from M_FT fti where fti.AS_ID= :AID
)
 and (RCE.DRV_DT, RCE.AID, RCE.R_CON_ID, RCE.R_CON_ver_nb) NOT IN
 (SELECT RCE_NS.DRV_DT, RCE_NS.AID, RCE_NS.R_CON_ID, RCE_NS.R_CON_VER_NB
  FROM R_CON_E RCE_NS
  WHERE RCE_NS.MTNE_ID NOT IN (select MI2.MTNE_ID
 from M_INF MI2  where MI2.AID = :AID
  UNION
  SELECT    RG2.RG_MF_SK_ID
from RG_M_F_INF RG2 where   RG2.AS_ID =:AID
 UNION
 SELECT    FTI1.FT_SRK_ID
 from M_FT FTI1  where FTI1.AS_ID= :AID
))
) b
where RC_STS.RR_FRQ_NB = D_LKP_FRQ.D_LKP_NB
  and RC_STS.R_CON_ESTS_NB = D_LKP_STS.D_LKP_NB
  and RC_STS.R_CON_ID = rc.R_CON_ID
  and RC_STS.R_CON_VER_NB = rc.R_CON_VER_NB
  and RC_STS.AID = rc.AID
  and RC_STS.AID = b.AID
  and RC_STS.R_CON_ID = b.R_CON_ID
  and RC_STS.R_CON_VER_NB = b.R_CON_VER_NB
order by 3,4,2;

-> Sort: RC_STS.R_CON_ID, RC_STS.R_CON_VER_NB, RC_STS.R_EX_RID  (actual
time=44392.655..44644.844 rows=745483 loops=1)
    -> Stream results  (cost=311479029610.37 rows=860847650219) (actual
time=8957.556..42133.969 rows=745483 loops=1)
        -> Nested loop inner join  (cost=311479029610.37 rows=860847650219)
(actual time=8957.548..40891.903 rows=745483 loops=1)
            -> Nested loop inner join  (cost=225393084569.25
rows=860847650219) (actual time=8957.541..40597.741 rows=745483 loops=1)
                -> Nested loop inner join  (cost=139307139528.12
rows=860847650219) (actual time=8957.530..40092.267 rows=745483 loops=1)
                    -> Nested loop antijoin  (cost=53221194487.00
rows=532199430400) (actual time=8957.477..29529.382 rows=671352 loops=1)

*                        -> Nested loop inner join  (cost=886687.00
rows=729520) (actual time=0.123..19714.306 rows=692583 loops=1)
                -> Filter: <in_optimizer>(RCE.MTNE_ID,<exists>(select #3))
 (cost=84215.00 rows=729520) (actual time=0.085..9045.124 rows=692583
loops=1)*                                -> Covering index scan on RCE
using R_58  (cost=84215.00 rows=729520) (actual time=0.055..534.110
rows=742706 loops=1)
                                -> Select #3 (subquery in condition;
dependent)
                                    -> Limit: 1 row(s)  (cost=4.41..4.41
rows=1) (actual time=0.010..0.010 rows=1 loops=742706)
                                        -> Table scan on <union temporary>
 (cost=4.41..5.70 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
                                            -> Union materialize with
deduplication  (cost=3.18..3.18 rows=2) (actual time=0.010..0.010 rows=1
loops=742706)
                                                -> Limit table size: 1
unique row(s)
                                                   -> Limit: 1 row(s)
 (cost=1.13 rows=1) (actual time=0.006..0.006 rows=1 loops=742706)
                                                        -> Covering index
lookup on mi1 using M_INF_AID_index (AID='XXXXXXXXXXXXXXXXXXX',
MTNE_ID=<cache>(RCE.MTNE_ID))  (cost=1.13 rows=1) (actual time=0.006..0.006
rows=1 loops=742706)
                                                -> Limit table size: 1
unique row(s)
                                                    -> Limit: 1 row(s)
 (cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
                                                        -> Single-row
covering index lookup on rg using PRIMARY
(RG_MF_SK_ID=<cache>(RCE.MTNE_ID), AS_ID='XXXXXXXXXXXXXXXXXXX')  (cost=1.10
rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
                                                -> Limit table size: 1
unique row(s)
                                                    -> Limit: 1 row(s)
 (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
                                                        -> Filter:
(fti.AS_ID = 'XXXXXXXXXXXXXXXXXXX')  (cost=0.74 rows=0.05) (actual
time=0.003..0.003 rows=0 loops=50123)
                                                            -> Covering
index lookup on fti using AK_MFTI (FT_SRK_ID=<cache>(RCE.MTNE_ID))
 (cost=0.74 rows=2) (actual time=0.003..0.003 rows=0 loops=50123)
                            -> Index lookup on rc using R_26 (AID=RCE.AID,
R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB)  (cost=1.00 rows=1)
(actual time=0.014..0.015 rows=1 loops=692583)
                         -> Single-row index lookup on <subquery7> using
<auto_distinct_key> (DRV_DT=RCE.DRV_DT, AID=RCE.AID, R_CON_ID=RCE.R_CON_ID,
R_CON_VER_NB=RCE.R_CON_VER_NB)  (cost=157167.31..157167.31 rows=1) (actual
time=0.014..0.014 rows=0 loops=692583)
                            -> Materialize with deduplication
 (cost=157167.00..157167.00 rows=729520) (actual time=8957.347..8957.347
rows=25843 loops=1)

*                                -> Filter: ((RCE_NS.DRV_DT is not null)
and (RCE_NS.AID is not null) and (RCE_NS.R_CON_ID is not null) and
(RCE_NS.R_CON_VER_NB is not null))  (cost=84215.00 rows=729520) (actual
time=1737.420..8871.505 rows=50123 loops=1)
    -> Filter: <in_optimizer>(RCE_NS.MTNE_ID,<exists>(select #8) is false)
 (cost=84215.00 rows=729520) (actual time=1737.417..8860.489 rows=50123
loops=1)*                                        -> Covering index scan on
RCE_NS using R_58  (cost=84215.00 rows=729520) (actual time=0.039..531.571
rows=742706 loops=1)
                                        -> Select #8 (subquery in
condition; dependent)
                                            -> Limit: 1 row(s)
 (cost=4.41..4.41 rows=1) (actual time=0.010..0.010 rows=1 loops=742706)
                                                -> Table scan on <union
temporary>  (cost=4.41..5.70 rows=2) (actual time=0.010..0.010 rows=1
loops=742706)
                                                    -> Union materialize
with deduplication  (cost=3.18..3.18 rows=2) (actual time=0.010..0.010
rows=1 loops=742706)
                                                        -> Limit table
size: 1 unique row(s)
                                                         -> Limit: 1 row(s)
 (cost=1.13 rows=1) (actual time=0.007..0.007 rows=1 loops=742706)
                                                                -> Covering
index lookup on MI2 using M_INF_AID_index (AID='XXXXXXXXXXXXXXXXXXX',
MTNE_ID=<cache>(RCE_NS.MTNE_ID))  (cost=1.13 rows=1) (actual
time=0.006..0.006 rows=1 loops=742706)
                                                        -> Limit table
size: 1 unique row(s)
                                                            -> Limit: 1
row(s)  (cost=1.10 rows=1) (actual time=0.004..0.004 rows=1 loops=132294)
                                                                ->
Single-row covering index lookup on RG2 using PRIMARY
(RG_MF_SK_ID=<cache>(RCE_NS.MTNE_ID), AS_ID='XXXXXXXXXXXXXXXXXXX')
 (cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
                                                        -> Limit table
size: 1 unique row(s)
                                                            -> Limit: 1
row(s)  (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
                                                                -> Filter:
(FTI1.AS_ID = 'XXXXXXXXXXXXXXXXXXX')  (cost=0.74 rows=0.05) (actual
time=0.003..0.003 rows=0 loops=50123)
                                                                    ->
Covering index lookup on FTI1 using AK_MFTI
(FT_SRK_ID=<cache>(RCE_NS.MTNE_ID))  (cost=0.74 rows=2) (actual
time=0.003..0.003 rows=0 loops=50123)
                    -> Index lookup on RC_STS using RCE_STS (AID=RCE.AID,
R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB)  (cost=1.62 rows=2)
(actual time=0.013..0.016 rows=1 loops=671352)
                -> Single-row index lookup on D_LKP_STS using PRIMARY
(D_LKP_NB=RC_STS.R_CON_ESTS_NB)  (cost=1.00 rows=1) (actual
time=0.000..0.000 rows=1 loops=745483)
            -> Single-row index lookup on D_LKP_FRQ using PRIMARY
(D_LKP_NB=RC_STS.RR_FRQ_NB)  (cost=1.00 rows=1) (actual time=0.000..0.000
rows=1 loops=745483)

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 16 Oct 2024 11:03:10 -0700

On 10/16/24 10:50 AM, yudhi s wrote:
> Below is a query which is running for ~40 seconds. As it's a query which 
> is executed from UI , we were expecting it to finish in <~5 seconds. It 
> has a "IN" and a "NOT IN" subquery , from the execution path it seems 
> the total response time is mainly, to be sum of the "IN" and the "NOT 
> IN" subquery section. My thought was that both "IN" and "NOT IN" should 
> be executed/evaluated in parallel but not in serial fashion.
> 
> In the execution path below , the line number marked in *bold* are the 
> top lines for the IN and NOT IN subquery evaluation and they are showing 
> summed up and the top lines showing it to be ~19 seconds. Then onwards 
> it keeps on increasing with other "nested loop" joins.
> 
> *Note*:- This query is running on a MYSQL 8.0 database. So I'm wondering 
> if there is any mysql list similar to Oracle list , in which i can share 
> this issue?
> 

This is the Postgres list.

As to your question maybe one of the forums?:

https://forums.mysql.com/

In particular Performance:

https://forums.mysql.com/list.php?24

-- 
Adrian Klaver
[email-masked]

---

From: "Peter J. Holzer" <[email-masked]>
Date: Wed, 16 Oct 2024 23:36:10 +0200

On 2024-10-16 23:20:36 +0530, yudhi s wrote:
> Below is a query which is running for ~40 seconds.
[...]
> In the execution path below , the line number marked in bold are the top lines
> for the IN and NOT IN subquery evaluation and they are showing "Actual time" as
>  Approx ~9 seconds and ~8 seconds and they seems to be summed up and the top
> lines showing it to be ~19 seconds. Then onwards it keeps on increasing with
> other "nested loop" joins.
> 
> Note:- This query is running on a MYSQL 8.0 database. So I'm wondering if there
> is any mysql list similar to Oracle list , in which i can share this issue?

The execution plan looks like a postgresql execution plan, not a mysql
execution plan. Did you run this query on postgresql? That may be
interesting for comparison purposese, but ultimately it is useless: You
won't get mysql to work like postgresql, and any tips to speed up this
query on postgresql (which is all you can expect on a postgresql mailing
list) probably won't work on mysql.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: yudhi s <[email-masked]>
Date: Thu, 17 Oct 2024 11:57:24 +0530

> list) probably won't work on mysql.

Agreed. Postgres and mysql may have differences in how the optimizer is
interpreting the stats and coming up with the execution oath. However, I
was looking if the query can be written efficiently by tweaking the current
logic. It's actually spending the majority of the time doing the "IN" and
"NOT IN" evaluation and in that it's using the same exact subquery for the
"UNION ALL" . And the overall execution time is summation of the IN and NOT
IN clause evaluation. So  I was thinking of a better way of writing the
same logically.