From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | ladayaroslav(at)yandex(dot)ru |
Subject: | BUG #15947: Worse plan is chosen after giving the planner more freedom (partitionwise join) |
Date: | 2019-08-10 22:28:08 |
Message-ID: | 15947-c242874031212a07@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: 15947
Logged by: Yaroslav Schekin
Email address: ladayaroslav(at)yandex(dot)ru
PostgreSQL version: 11.5
Operating system: Any
Description:
After creating of the tables below:
-----
CREATE TABLE sg (
id bigint NOT NULL,
sc_fk bigint,
geo_id bigint,
sl smallint NOT NULL,
a date NOT NULL,
o boolean NOT NULL
)
PARTITION BY RANGE (o, sl, a);
CREATE TABLE sg_19_01_d PARTITION OF sg FOR VALUES FROM (false, '5',
'2019-01-01') TO (false, '5', '2019-02-01');
CREATE TABLE sg_19_02_d PARTITION OF sg FOR VALUES FROM (false, '5',
'2019-02-01') TO (false, '5', '2019-03-01');
CREATE TABLE sc (
id bigint,
a date NOT NULL,
sl smallint NOT NULL,
o boolean NOT NULL
)
PARTITION BY RANGE (o, sl, a);
CREATE TABLE sc_19_01_d PARTITION OF sc FOR VALUES FROM (false, '5',
'2019-01-01') TO (false, '5', '2019-02-01');
CREATE TABLE sc_19_02_d PARTITION OF sc FOR VALUES FROM (false, '5',
'2019-02-01') TO (false, '5', '2019-03-01');
INSERT INTO sg_19_01_d(id, sc_fk, geo_id, sl, a, o)
SELECT n, n, 0, 5, '2019-01-01', false
FROM generate_series(1, 1000) AS g(n);
INSERT INTO sg_19_02_d(id, sc_fk, geo_id, sl, a, o)
SELECT n, n, 0, 5, '2019-02-01', false
FROM generate_series(1, 1000) AS g(n);
INSERT INTO sc_19_01_d(id, a, sl, o)
SELECT n, '2019-01-01', 5, false
FROM generate_series(1, 1000) AS g(n);
INSERT INTO sc_19_02_d(id, a, sl, o)
SELECT n, '2019-02-01', 5, false
FROM generate_series(1, 1000) AS g(n);
ANALYZE sg_19_01_d, sg_19_02_d, sc_19_01_d, sc_19_02_d;
-----
I'm trying the following query:
EXPLAIN
SELECT COUNT(*)
FROM sc
WHERE EXISTS (
SELECT 1
FROM sg
WHERE sc.id = sg.sc_fk
AND sc.a = sg.a
AND sc.o = sg.o
AND sc.sl = sg.sl
);
Which produces the plan with this cost estimation (top node):
-- Aggregate (cost=147.25..147.26 rows=1 width=8)
But after:
SET enable_partitionwise_join = true;
The new plan is more expensive:
-- Aggregate (cost=175.00..175.01 rows=1 width=8)
This shouldn't be happening, right?
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Rijkers | 2019-08-10 23:54:01 | Re: BUG #15947: Worse plan is chosen after giving the planner more freedom (partitionwise join) |
Previous Message | Tom Lane | 2019-08-10 14:23:16 | Re: BUG #15946: "duplicate key" error on ANALYZE of table partitions in transaction |