parts-problem.sql

DROP TABLE IF EXISTS parent CASCADE;
DROP FUNCTION IF EXISTS pruning_delayer;

\set parts 3000
CREATE TABLE parent (
  x integer,
  y integer,
  z integer,
  q integer,
  CONSTRAINT pk_h_ PRIMARY KEY (x, y, z)
) PARTITION BY RANGE (z) ;

SELECT format('
			  CREATE TABLE part_%s
			  PARTITION OF parent
			  FOR VALUES FROM (%s) TO (%s);',
			  x, x, x+1)
FROM generate_series(1, :parts ,1) x
\gexec
ANALYZE parent;

CREATE OR REPLACE FUNCTION pruning_delayer() RETURNS integer AS $$
BEGIN
	RETURN 100000;
END;
$$ LANGUAGE plpgsql STABLE;

-- -----------------------------------------------------------------------------

explain (analyze, costs off)
SELECT *
FROM parent
WHERE
  z > pruning_delayer() AND -- No initial pruning, but execution pruning because of STABLE function in the clause
  x IN (SELECT x FROM generate_series(100,102,1) AS x) AND -- No pruning because of a JOIN
  y=1
;

explain (analyze, costs off)
SELECT *
FROM parent
WHERE
  z > pruning_delayer() AND -- No initial pruning, but execution pruning because of STABLE function in the clause
  x IN (SELECT x FROM generate_series(100,102,1) AS x) AND -- No pruning because of a JOIN
  y=1 AND
  q=2 AND
  q*q=4
;
  
explain (analyze, verbose, costs off)
SELECT *
FROM parent
WHERE
  z > pruning_delayer() AND -- No initial pruning, but execution pruning because of STABLE function in the clause
  x IN (SELECT x FROM generate_series(100,102,1) AS x) AND -- No pruning because of a JOIN
  y=1 AND
  q=2 AND
  q*q=4 AND
  q*q*q=8 AND
  q*q*q*q=16 AND
  q*q*q*q*q=32 AND
  q*q*q*q*q*q=64 AND
  q*q*q*q*q*q*q=128 AND
  q*q*q*q*q*q*q*q=256
;