job_workflow_analysis

It is designed to check the behaviour of error criteria on complex queries.

Executes on each push into the main branch.

Links:

• The workflow's script.

After the test passes, a dump of the extension's output is saved as an artifact, available for a week.

How to evaluate results

At first, create a schema to copy the data. For example, you may use the following SQL statement (may float with changed interface - see report page for detailed explanation):

CREATE TABLE job_tracking_data (
  queryid          bigint,
  query            text,

  /* Average error statistics */
  avg_min          double precision,
  avg_max          double precision,
  avg_cnt          double precision,
  avg_mean         double precision,
  avg_stddev       double precision,

  /* RMS error statistics */
  rms_min          double precision,
  rms_max          double precision,
  rms_cnt          double precision,
  rms_mean         double precision,
  rms_stddev       double precision,

  /* Time-weighted average error statistics */
  twa_min          double precision,
  twa_max          double precision,
  twa_cnt          double precision,
  twa_mean         double precision,
  twa_stddev       double precision,

  /* Planning error, weighted by cost factor */
  wca_min          double precision,
  wca_max          double precision,
  wca_cnt          double precision,
  wca_mean         double precision,
  wca_stddev       double precision,

  /* Blocks statistics */
  blks_min         double precision,
  blks_max         double precision,
  blks_cnt         double precision,
  blks_avg         double precision,
  blks_dev         double precision,

  /* Local blocks statistics (work_mem indicator) */
  local_min        double precision,
  local_max        double precision,
  local_cnt        double precision,
  local_mean       double precision,
  local_stddev     double precision,

  /* Execution time statistics (milliseconds) */
  time_min         double precision,
  time_max         double precision,
  time_cnt         double precision,
  time_mean        double precision,
  time_stddev      double precision,

  /* Maximum JOIN filtered rows statistics */
  jf_min           double precision,
  jf_max           double precision,
  jf_cnt           double precision,
  jf_mean          double precision,
  jf_stddev        double precision,

  /* Maximum leaf node filtered rows statistics */
  lf_min           double precision,
  lf_max           double precision,
  lf_cnt           double precision,
  lf_mean          double precision,
  lf_stddev        double precision,

  evaluated_nodes  integer,
  plan_nodes       integer,
  nexecs           bigint
);

And afterwards copy the data:

\copy job_tracking_data FROM 'pg_track_optimizer_results.csv' CSV HEADER;

A query to analyse these data may look like the following:

/* TOP-10 of worst queries according to 'avg_error' criteria */
SELECT queryid,avg_error,rms_error,twa_error,wca_error
FROM job_tracking_data
ORDER BY avg_error DESC LIMIT 10;

/* Intersection of TOP-10 worst queries by four criteria */
SELECT avg.* FROM (
  SELECT queryid,avg_error,rms_error,twa_error,wca_error
  FROM job_tracking_data
  ORDER BY avg_error DESC LIMIT 10) AS avg
JOIN (
  SELECT queryid,avg_error,rms_error,twa_error,wca_error
  FROM job_tracking_data
  ORDER BY rms_error DESC LIMIT 10) AS rms
USING (queryid)
JOIN
(
  SELECT queryid,avg_error,rms_error,twa_error,wca_error
  FROM job_tracking_data
  ORDER BY twa_error DESC LIMIT 10) AS twa
USING (queryid)
JOIN
(
  SELECT queryid,avg_error,rms_error,twa_error,wca_error
  FROM job_tracking_data
  ORDER BY wca_error DESC LIMIT 10) AS wca
USING (queryid)
ORDER BY queryid
;

And you should see something like the following:

       queryid        |     avg_error      |     rms_error     |     twa_error     |     wca_error      
----------------------+--------------------+-------------------+-------------------+--------------------
 -9016846908342554668 | 3.4533282552075293 | 4.833396520876712 | 2.056768995102834 | 2.5116267372393155
  3365438137355796538 | 4.1451151817014535 | 5.262296603465234 | 2.380242256585716 | 2.4047477634475523
  8417973092986304111 |  3.590109884060849 | 5.087571594527285 | 2.517998376394866 | 2.7178468983089616