Layer wfs:imos:soop_asf_mft_trajectory_data is slow

danfruehauf commented 10 years ago

https://nagios.aodn.org.au/pnp4nagios/graph?host=12-nsp-mel.emii.org.au&srv=geoserver+wfs%3Aimos%3Asoop_asf_mft_trajectory_data&view=3&start=&end=

Takes 2-3 seconds constantly. Can probably be optimized.

lbesnard commented 10 years ago

@danfruehauf AMAZON is the answer isn't it ?

danfruehauf commented 10 years ago

For this? Probably no :)

lbesnard commented 10 years ago

damn !

danfruehauf commented 9 years ago

This also ventured into the realm of "slow", not "semi-slow" any more. Takes 5 seconds usually: https://nagios.aodn.org.au/pnp4nagios/graph?host=12-nsp-mel.emii.org.au&srv=geoserver+wfs%3Aimos%3Asoop_asf_mft_trajectory_data&view=2&start=&end=

danfruehauf commented 9 years ago

The WFS view for this layer is:

 SELECT measurements.file_id,
    visualisation_wms.voyage_number AS cruise_id,
    visualisation_wms.vessel_name,
    visualisation_wms.platform_code,
    measurements.measurement,
    timezone('UTC'::text, measurements."TIME") AS "TIME",
    measurements."LATITUDE",
    measurements."LONGITUDE",
    measurements."AIRT",
    measurements."AIRT1_5M",
    measurements."AIRT2_0M",
    measurements."AIRT_FLAG",
    measurements."AIRT_H",
    measurements."ATMP",
    measurements."ATMP_FLAG",
    measurements."ATMP_H",
    measurements."HEAT_NET",
    measurements."HL",
    measurements."H_RAIN",
    measurements."HS",
    measurements."LW",
    measurements."LW_FLAG",
    measurements."LW_H",
    measurements."LW_NET",
    measurements."MASS_N",
    measurements."PL_CMP",
    measurements."PL_CRS",
    measurements."PL_SPD",
    measurements."RAIN_AMOUNT",
    measurements."RAIN_AMOUNT_FLAG",
    measurements."RAIN_AMOUNT_H",
    measurements."RELH",
    measurements."RELH1_5M",
    measurements."RELH2_0M",
    measurements."RELH_FLAG",
    measurements."RELH_H",
    measurements."SST",
    measurements."SW",
    measurements."SW_FLAG",
    measurements."SW_H",
    measurements."SW_NET",
    measurements."TAU",
    measurements."TEMP",
    measurements."TEMP_FLAG",
    measurements."TEMP_H",
    measurements."WDIR",
    measurements."WIND_F",
    measurements."WIND_H",
    measurements."WSPD",
    measurements."WSPD10M",
    measurements.geom
   FROM soop_asf_mft.measurements
     JOIN soop_asf_mft.visualisation_wms ON visualisation_wms.file_id = measurements.file_id
  ORDER BY measurements."TIME";

Can be easily solved by having a new index such as:

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

We can also notice that there is already an index on both TIME and file_id:

CREATE INDEX measurements_idx
  ON soop_asf_mft.measurements
  USING btree
  (file_id, "TIME");

Changing the view to ORDER BY on both file_id and TIME (the order matters!!) will also speed up the layer.

lbesnard commented 9 years ago

https://github.com/aodn/harvesters/pull/228 check with explain analyse on my local, heaps faster, ta

danfruehauf commented 9 years ago

Fix works in RC.

danfruehauf commented 9 years ago

Deployed to Prod. Fixed.

aodn / content

Layer wfs:imos:soop_asf_mft_trajectory_data is slow #50