INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

Running a query with PDQ enabled and whose execution plan
involves a nested loop join with a Time Series virtual table
whose base table is fragmented results in a large number of
sessions being created with each having a single thread named
ts_vtam_pdq. The majority of these sessions look to perform some
work and then remain idle until the query execution has
completed.

The sqexplain.out includes:

SELECT
aq_data_v.timestamp, aq_data.station_id,
  SUM(aq_data_v.pred_value),
  SUM(aq_data_v.obs_value),
  SUM(aq_data_v.inter_obs_value)
FROM aq_data_v aq_data_v
INNER JOIN aq_data aq_data
  ON aq_data_v.location_type = aq_data.location_type
  AND aq_data_v.station_id = aq_data.station_id
  AND aq_data_v.subdomain_id = aq_data.subdomain_id
  AND aq_data_v.sensor_parameter_code =
aq_data.sensor_parameter_code
WHERE aq_data_v.timestamp
  BETWEEN '2018-05-13 00:00:00' and '2018-05-13 23:59:59'
GROUP BY aq_data_v.timestamp, aq_data.station_id

  1) informix.aq_data: SEQUENTIAL SCAN  (Parallel, fragments:
ALL)

  2) informix.aq_data_v: VTI SCAN  (Parallel, fragments: ALL)

        VTI Filters:
(informix.equal(informix.aq_data_v.location_type,informix.a
q_data.location_type ) AND
informix.equal(informix.aq_data_v.station_id,informix
.aq_data.station_id ) AND
informix.equal(informix.aq_data_v.subdomain_id,informi
x.aq_data.subdomain_id ) AND
informix.equal(informix.aq_data_v.sensor_parameter_
code,informix.aq_data.sensor_parameter_code ) AND
informix.lessthanorequal(infor
mix.aq_data_v.timestamp,datetime(2018-05-13 23:59:59.00000) year
to fraction(5)
) AND
informix.greaterthanorequal(informix.aq_data_v.timestamp,datetim
e(2018-05-
13 00:00:00.00000) year to fraction(5) ))
NESTED LOOP JOIN

****************************************************************
* USERS AFFECTED:                                              *
* Users of IDS prior to 12.10.xC13.                            *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* PDQ query executed using nested loop join on Time Series     *
* virtual table may consume excessive resources.               *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

27.12.2018
03.10.2019
03.10.2019

12.10.xC13