IT27607: QUERY WITH REMOTE DISTRIBUTED TABLE TAKING WORSE PLAN WHEN DISTRIBUTIONS EXIST ON THE REMOTE TABLE THAN WHEN THE DISTRIBUTIONS

APAR status

• Closed as program error.

Error description

• The query is a 3 table join involving 2 local table and 1 remote
  table.
  
  When distributions exist in 12.10.xC7 the set explain output
  looks like this (and the query take > 30 minutes to complete)
  
  select ...
  FROM
          driver d,
          driver_tpay dt,
          person pn
  
  WHERE pn.person_id = dt.driver_id
  AND dt.tpay_type_code = 40
  AND pn.person_id = d.driver_id
  AND d.service_co_code='WM'
  AND d.vendor_nbr!=694000
  AND pn.win_nbr IS NOT NULL
  AND dt.paid_date =
          (select MAX(dt2.paid_date)  FROM driver_tpay dt2
            WHERE dt2.tpay_type_code = 40)
  AND dt.last_change_ts =
          (select MAX(dt3.last_change_ts)
            FROM driver_tpay dt3 WHERE dt3.driver_id =
  dt.driver_id
            AND dt3.tpay_type_code = dt.tpay_type_code
            AND dt3.paid_date = dt.paid_date)
  
  Estimated Cost: 2679008
  Estimated # of Rows Returned: 203
  
    1) jrenaut.d: SEQUENTIAL SCAN
  
          Filters: (jrenaut.d.service_co_code = 'WM' AND
  jrenaut.d.vendor_nbr != 694000 )
  
    2) jrenaut.pn: INDEX PATH
  
          Filters: jrenaut.pn.win_nbr IS NOT NULL
  
      (1) Index Name: jrenaut.person_pk
          Index Keys: person_id   (Serial, fragments: ALL)
          Lower Index Filter: jrenaut.pn.person_id =
  jrenaut.d.driver_id
  NESTED LOOP JOIN
  
    3) jrenaut.dt: REMOTE PATH
      REMOTE SESSION ID FOR 'walrem' is 35
  
      Remote SQL Request:
      select x2.payment_amt ,x2.driver_id ,x2.tpay_type_code
  ,x2.paid_date ,x2.last_change_ts from
  realdb:"jrenaut".driver_tpay x2 where ((((x2.tpay_type_code = 40
  ) AND (x2.paid_date = (select max(x0.paid_date ) from
  realdb:"jrenaut".driver_tpay x0 where (x0.tp
      ay_type_code = 40 ) ) ) ) AND (x2.last_change_ts = (select
  max(x1.last_change_ts ) from realdb:"jrenaut".driver_tpay x1
  where (((x1.driver_id = x2.driver_id ) AND (x1.paid_date =
  x2.paid_date ) ) AND (x1.tpay_type_code = 40 ) ) ) ) ) AND
  (x2.driver_id = ?
      ) )
  NESTED LOOP JOIN
  
      Subquery:
      ---------
      Estimated Cost: 2544447
      Estimated # of Rows Returned: 1
  
        1) jrenaut.dt2: REMOTE PATH
          REMOTE SESSION ID FOR 'walrem' is 35
  
  
      Subquery:
      ---------
      Estimated Cost: 239
      Estimated # of Rows Returned: 1
  
        1) jrenaut.dt3: REMOTE PATH
          REMOTE SESSION ID FOR 'walrem' is 35
  
  
  When the distributions are dropped on the remote table the set
  explain looks like this, and the query completes in ~1 minute
  
  select
  ...
  FROM
          driver d,
          driver_tpay dt,
          person pn
  
  WHERE pn.person_id = dt.driver_id
  AND dt.tpay_type_code = 40
  AND pn.person_id = d.driver_id
  AND d.service_co_code='WM'
  AND d.vendor_nbr!=694000
  AND pn.win_nbr IS NOT NULL
  AND dt.paid_date =
          (select MAX(dt2.paid_date)  FROM driver_tpay dt2
            WHERE dt2.tpay_type_code = 40)
  AND dt.last_change_ts =
          (select MAX(dt3.last_change_ts)
            FROM driver_tpay dt3 WHERE dt3.driver_id =
  dt.driver_id
            AND dt3.tpay_type_code = dt.tpay_type_code
            AND dt3.paid_date = dt.paid_date)
  
  Estimated Cost: 97546
  Estimated # of Rows Returned: 1
  
    1) jrenaut.d: SEQUENTIAL SCAN
  
          Filters: (jrenaut.d.service_co_code = 'WM' AND
  jrenaut.d.vendor_nbr != 694000 )
  
    2) jrenaut.pn: INDEX PATH
          Filters: jrenaut.pn.win_nbr IS NOT NULL
  
      (1) Index Name: jrenaut. 101_4
          Index Keys: person_id   (Serial, fragments: ALL)
          Lower Index Filter: jrenaut.pn.person_id =
  jrenaut.d.driver_id
  NESTED LOOP JOIN
  
    3) jrenaut.dt: REMOTE PATH
      REMOTE SESSION ID FOR 'walrem' is 44
  
      Remote SQL Request:
      select x2.payment_amt ,x2.driver_id ,x2.tpay_type_code
  ,x2.paid_date ,x2.last_change_ts from
  waldata:"jrenaut".driver_tpay x2 where ((x2.tpay_type_code = 40
  ) AND (x2.paid_date = (select max(x0.paid_date ) from
  waldata:"jrenaut".driver_tpay x0 where (x0.tp
      ay_type_code = 40 ) ) ) )
          Filters: jrenaut.dt.last_change_ts = <subquery>
  
      (1) Index Name: driver_tpay_idx2
          Index Keys: driver_id tpay_type_code
          Lower Index Filter: (jrenaut.dt.driver_id =
  jrenaut.d.driver_id AND jrenaut.dt.tpay_type_code = 40 )
  NESTED LOOP JOIN
  
      Subquery:
      ---------
      Estimated Cost: 81142
      Estimated # of Rows Returned: 1
  
        1) jrenaut.dt2: REMOTE PATH
          REMOTE SESSION ID FOR 'walrem' is 44
  
  
      Subquery:
      ---------
      Estimated Cost: 6
      Estimated # of Rows Returned: 1
        1) jrenaut.dt3: REMOTE PATH
          REMOTE SESSION ID FOR 'walrem' is 44
  
          Remote SQL Request:
          select max(x1.last_change_ts ) from
  waldata:"jrenaut".driver_tpay x1 where (((x1.driver_id = ? ) AND
  (x1.tpay_type_code = 40 ) ) AND (x1.paid_date = ? ) )
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of IDS prior to 12.10.xC13.                            *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Query with remote distributed table taking worse plan when   *
  * distributions exist on the remote table than when the        *
  * distributions are dropped.                                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  

Problem conclusion

• Fixed in IDS 12.10.xC13.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels