Layer wfs:imos:soop_co2_trajectory_data slow

[danfruehauf]

Something can probably be optimized over there. Takes 5-10 seconds to load most of the time: https://nagios.aodn.org.au/pnp4nagios/graph?host=1-aws-syd.emii.org.au&srv=geoserver+wfs%3Aimos%3Asoop_co2_trajectory_data&view=1&start=&end=

On systest even a bit slower: https://nagios.aodn.org.au/pnp4nagios/graph?host=5-nec-mel.emii.org.au&srv=geoserver+wfs%3Aimos%3Asoop_co2_trajectory_data&view=2&start=&end=

[danfruehauf]

This has gone to the slow zone now. This is not semi-slow any more.

[danfruehauf]

Query that's being run in DB is:

SELECT "file_id","vessel_name","cruise_id","measurement","TIME","TIME_quality_control","LATITUDE","LATITUDE_quality_control","LONGITUDE","LONGITUDE_quality_control",
"DfCO2","DfCO2_quality_control","fCO2ATM_UATM_INTERPOLATED","fCO2ATM_UATM_INTERPOLATED_quality_control","fCO2SW_UATM","fCO2SW_UATM_quality_control","H2OFLOW","H2OFLOW_quality_control",
"LICORflow","LICORflow_quality_control","Press_ATM","Press_ATM_quality_control","Press_Equil","Press_Equil_quality_control","PSAL","PSAL_quality_control","SUBFLAG","TEMP_1","TEMP_1_quality_control",
"TEMP_2","TEMP_2_quality_control","WDIR","WDIR_quality_control","WSPD","WSPD_quality_control","xCO2ATM_PPM","xCO2ATM_PPM_quality_control","xCO2ATM_PPM_INTERPOLATED","xCO2ATM_PPM_INTERPOLATED_quality_control",
"xCO2EQ_PPM","xCO2EQ_PPM_quality_control",encode(ST_AsEWKB("geom"),'base64') as "geom"
FROM "soop_co2"."soop_co2_trajectory_data" LIMIT 1

[danfruehauf]

The WFS view looks like:

 SELECT measurements.file_id,
    visualisation_wms.vessel_name,
    visualisation_wms.voyage_number AS cruise_id,
    measurements.measurement,
    timezone('UTC'::text, measurements."TIME") AS "TIME",
    measurements."TIME_quality_control",
    measurements."LATITUDE",
    measurements."LATITUDE_quality_control",
    measurements."LONGITUDE",
    measurements."LONGITUDE_quality_control",
    measurements."DfCO2",
    measurements."DfCO2_quality_control",
    measurements."fCO2ATM_UATM_INTERPOLATED",
    measurements."fCO2ATM_UATM_INTERPOLATED_quality_control",
    measurements."fCO2SW_UATM",
    measurements."fCO2SW_UATM_quality_control",
    measurements."H2OFLOW",
    measurements."H2OFLOW_quality_control",
    measurements."LICORflow",
    measurements."LICORflow_quality_control",
    measurements."Press_ATM",
    measurements."Press_ATM_quality_control",
    measurements."Press_Equil",
    measurements."Press_Equil_quality_control",
    measurements."PSAL",
    measurements."PSAL_quality_control",
    measurements."SUBFLAG",
    measurements."TEMP_1",
    measurements."TEMP_1_quality_control",
    measurements."TEMP_2",
    measurements."TEMP_2_quality_control",
    measurements."WDIR",
    measurements."WDIR_quality_control",
    measurements."WSPD",
    measurements."WSPD_quality_control",
    measurements."xCO2ATM_PPM",
    measurements."xCO2ATM_PPM_quality_control",
    measurements."xCO2ATM_PPM_INTERPOLATED",
    measurements."xCO2ATM_PPM_INTERPOLATED_quality_control",
    measurements."xCO2EQ_PPM",
    measurements."xCO2EQ_PPM_quality_control",
    measurements.geom
   FROM soop_co2.measurements
     JOIN soop_co2.visualisation_wms ON visualisation_wms.file_id = measurements.file_id
  ORDER BY measurements."TIME";

This can be easily solved by having a new index on TIME such as:

CREATE INDEX "measurements_TIME_idx"
  ON soop_co2.measurements
  USING btree
  ("TIME");

[lbesnard]

https://github.com/aodn/harvesters/pull/229

[danfruehauf]

Fix works in RC.

[danfruehauf]

Deployed to Prod. Fixed.