aqo--1.3--1.4.sql

/* contrib/aqo/aqo--1.3--1.4.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "ALTER EXTENSION aqo UPDATE TO '1.4'" to load this file. \quit

ALTER TABLE public.aqo_data ADD COLUMN reliability double precision [];

DROP FUNCTION public.top_error_queries(int);

--
-- Get cardinality error of queries the last time they were executed.
-- IN:
-- controlled - show queries executed under a control of AQO (true);
-- executed without an AQO control, but AQO has a stat on the query (false).
--
-- OUT:
-- num - sequental number. Smaller number corresponds to higher error.
-- id - ID of a query.
-- fshash - feature space. Usually equal to zero or ID.
-- error - AQO error that calculated on plan nodes of the query.
-- nexecs - number of executions of queries associated with this ID.
--
CREATE OR REPLACE FUNCTION public.show_cardinality_errors(controlled boolean)
RETURNS TABLE(num bigint, id bigint, fshash bigint, error float, nexecs bigint)
AS $$
BEGIN
IF (controlled) THEN
  RETURN QUERY
    SELECT
      row_number() OVER (ORDER BY (cerror, query_id, fs_hash) DESC) AS nn,
      query_id, fs_hash, cerror, execs
    FROM (
    SELECT
      aq.query_hash AS query_id,
      aq.fspace_hash AS fs_hash,
      cardinality_error_with_aqo[array_length(cardinality_error_with_aqo, 1)] AS cerror,
      executions_with_aqo AS execs
    FROM public.aqo_queries aq JOIN public.aqo_query_stat aqs
    ON aq.query_hash = aqs.query_hash
    WHERE TRUE = ANY (SELECT unnest(cardinality_error_with_aqo) IS NOT NULL)
    ) AS q1
    ORDER BY nn ASC;
ELSE
  RETURN QUERY
    SELECT
      row_number() OVER (ORDER BY (cerror, query_id, fs_hash) DESC) AS nn,
      query_id, fs_hash, cerror, execs
    FROM (
      SELECT
        aq.query_hash AS query_id,
        aq.fspace_hash AS fs_hash,
        array_avg(cardinality_error_without_aqo) AS cerror,
        executions_without_aqo AS execs
      FROM public.aqo_queries aq JOIN public.aqo_query_stat aqs
      ON aq.query_hash = aqs.query_hash
      WHERE TRUE = ANY (SELECT unnest(cardinality_error_without_aqo) IS NOT NULL)
      ) AS q1
    ORDER BY (nn) ASC;
END IF;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION public.show_cardinality_errors(boolean) IS
'Get cardinality error of queries the last time they were executed. Order queries according to an error value.';