From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | dbman(at)sqlexec(dot)com |
Subject: | BUG #15917: Runtime Partition Pruning does not seem to work in PG 11 |
Date: | 2019-07-18 13:04:15 |
Message-ID: | 15917-08e28969f78247e4@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15917
Logged by: Michael Vitale
Email address: dbman(at)sqlexec(dot)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';
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2019-07-18 13:06:18 | Re: BUG #15908: Xpath operations fail |
Previous Message | Manuel Rigger | 2019-07-18 12:50:06 | Re: ERROR: negative bitmapset member not allowed in SELECT |