BUG #15947: Worse plan is chosen after giving the planner more freedom (partitionwise join)
От | PG Bug reporting form |
---|---|
Тема | BUG #15947: Worse plan is chosen after giving the planner more freedom (partitionwise join) |
Дата | |
Msg-id | 15947-c242874031212a07@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15947: Worse plan is chosen after giving the planner morefreedom (partitionwise join)
Re: BUG #15947: Worse plan is chosen after giving the planner more freedom (partitionwise join) |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 15947 Logged by: Yaroslav Schekin Email address: ladayaroslav@yandex.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?
В списке pgsql-bugs по дате отправления: