CREATE EXTENSION IF NOT EXISTS tablefunc;
DROP TABLE IF EXISTS normal_0_50;

CREATE TABLE normal_0_50(x int);
INSERT INTO normal_0_50 SELECT normal_rand(1000000, 0, 50);
CREATE INDEX normal_0_50_x_idx ON normal_0_50(x);

ALTER TABLE normal_0_50 ALTER COLUMN x SET STATISTICS 1000;
ANALYSE normal_0_50;

SELECT array_agg(unnest) AS mcvs FROM (
  SELECT unnest(string_to_array(array_to_string(most_common_vals, ','), ','))::int
    FROM pg_stats WHERE tablename = 'normal_0_50' ORDER BY 1) t;
SELECT cardinality(most_common_vals)
  FROM pg_stats WHERE tablename = 'normal_0_50';
SELECT histogram_bounds FROM pg_stats WHERE tablename = 'normal_0_50';

DO
$$
DECLARE
  p text;
  estimate bigint;
  actual bigint;
BEGIN
  FOR x IN -250..250 LOOP
    EXECUTE 'EXPLAIN ANALYSE SELECT * FROM normal_0_50 WHERE x = $1' USING x INTO p;
    p := regexp_replace(p, '.*rows=([0-9]*).*rows=([0-9]*).*', '\1,\2');
    estimate := (string_to_array(p, ','))[1];
    actual := (string_to_array(p, ','))[2];
    RAISE NOTICE 'x=%, estimate=%, actual=%, error=%, in_mcv_list=%',
                 x, estimate, actual,
                 CASE
                   WHEN actual > 0 THEN
                     round((estimate-actual)*100.0/actual, 2)||'%'
                   ELSE 'Inf' 
                 END,
                 (SELECT x =ANY (string_to_array(array_to_string((
                                   most_common_vals), ','), ',')::int[])
                    FROM pg_stats WHERE tablename = 'normal_0_50');
  END LOOP;
END;
$$ LANGUAGE plpgsql;
