Thread: BUG #15917: Runtime Partition Pruning does not seem to work in PG 11

The following bug has been logged on the website:

Bug reference:      15917
Logged by:          Michael Vitale
Email address:      dbman@sqlexec.com
PostgreSQL version: 11.4
Operating system:   Ubuntu 16.04.6 LTS
Description:

I am unable to get "runtime partition pruning" working in PG11.  I can
reproduce the problem.  The following DDL/DML will create a 100,000 row
partitioned table with about 8-9 thousand rows for each month of year 2019.
I then try to join with a non-partitioned table that has 3 rows with dates
for the first 3 months of 2019 hoping that runtime partition pruning will
only show the first 3 partitions are being considered or used.  They are
not.

set search_path='testing'; 
CREATE TYPE cycle_frequency AS ENUM ('WEEKLY', 'MONTHLY', 'QUARTERLY',
'ANNUALLY');
CREATE TABLE test_result (
  id bigint GENERATED BY DEFAULT AS IDENTITY,
  partition_id bigint NOT NULL,
  test_start_date timestamp with time zone NOT NULL,
  test_frequency  cycle_frequency NOT NULL,
  created timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
  port integer,
  ignored boolean DEFAULT false NOT NULL,
  additional_data text
  )  PARTITION BY LIST (test_frequency) WITH (OIDS) TABLESPACE pg_default;
ALTER TABLE test_result ADD CONSTRAINT test_result_pkey PRIMARY KEY (id,
test_frequency, test_start_date, partition_id) WITH (fillfactor=80);

-- creating 1 cycle_frequency partitions...
CREATE TABLE test_result_monthly partition of test_result FOR VALUES IN
('MONTHLY') PARTITION BY RANGE (test_start_date);
-- creating 12 monthly partitions...
CREATE TABLE test_result_monthly_2019_01 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
CREATE TABLE test_result_monthly_2019_02 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');
CREATE TABLE test_result_monthly_2019_03 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');
CREATE TABLE test_result_monthly_2019_04 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-04-01') TO ('2019-05-01');
CREATE TABLE test_result_monthly_2019_05 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-05-01') TO ('2019-06-01');
CREATE TABLE test_result_monthly_2019_06 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-06-01') TO ('2019-07-01');
CREATE TABLE test_result_monthly_2019_07 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-07-01') TO ('2019-08-01');
CREATE TABLE test_result_monthly_2019_08 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-08-01') TO ('2019-09-01');
CREATE TABLE test_result_monthly_2019_09 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-09-01') TO ('2019-10-01');
CREATE TABLE test_result_monthly_2019_10 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-10-01') TO ('2019-11-01');
CREATE TABLE test_result_monthly_2019_11 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-11-01') TO ('2019-12-01');
CREATE TABLE test_result_monthly_2019_12 PARTITION OF test_result_monthly
FOR VALUES FROM ('2019-12-01') TO ('2020-01-01');

-- populate the partitioned tables
INSERT INTO test_result (id, partition_id, test_start_date, test_frequency,
ignored) SELECT generate_series(1, 100000), 1, generate_series('2019-01-01
00:00'::timestamp,'2019-12-31 12:00', '5 minute'), 'MONTHLY', 'f' limit
100000;

create table testjoin(id int not null, adate timestamp not null);
insert into testjoin(id, adate) VALUES (3, '2019-01-01');
insert into testjoin(id, adate) VALUES (3, '2019-02-01');
insert into testjoin(id, adate) VALUES (3, '2019-03-01');
vacuum analyze test_result;
vacuum analyze testjoin;

Run queries to test it.
-- does index scan on all months, not just months, 1,2,3 so runtime
partition exclusion seems not to work
explain (verbose, costs, timing, buffers, summary, analyze) select a.* from
test_result a, testjoin b where a.test_start_date  = b.adate;

-- does not work for these variants either
explain (verbose, costs, timing, buffers, summary, analyze) select a.* from
test_result a where a.test_start_date = (select adate from testjoin limit
1);
explain (verbose, costs, timing, buffers, summary, analyze) select a.* from
test_result a where a.test_start_date IN (select adate from testjoin);

-- does as expected only using months 1,2,3 but only if the date is
explicitly provided in the WHERE clause.
explain (verbose, costs, timing, buffers, summary, analyze) select a.* from
test_result a, testjoin b where a.test_start_date  = b.adate and
a.test_start_date between '2019-01-01' and '2019-03-01';