PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15947
PG Version11.5
OSAny
Opened2019-08-10 22:28:08+00
Reported byYaroslav Schekin
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15947
Logged by:          Yaroslav Schekin
Email address:      (redacted)
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?

Messages

DateAuthorSubject
2019-08-10 22:28:08+00PG Bug reporting formBUG #15947: Worse plan is chosen after giving the planner more freedom (partitionwise join)
2019-08-10 23:54:01+00Erik RijkersRe: BUG #15947: Worse plan is chosen after giving the planner more freedom (partitionwise join)
2019-08-11 16:39:17+00Tom LaneRe: BUG #15947: Worse plan is chosen after giving the planner more freedom (partitionwise join)
2019-08-14 00:46:16+00KeithRe: BUG #15947: Worse plan is chosen after giving the planner more freedom (partitionwise join)