BUG #14404: High row estimates when query uses master inherited tables
The following bug has been logged on the website:
Bug reference: 14404
Logged by: Clinton Adams
Email address: clinton.adams@gmail.com
PostgreSQL version: 9.6.1
Operating system: centos 7, 4.3.0-1.el7.elrepo.x86_64 #1 SMP Tue Nov
Description:
Row estimates are way off (406484054678631 vs 38) when using master
partition tables. If I change the query to go directly against one child
table, estimates and query time are in line with what I expect.
Issue occurs on 9.6.1, all tables analyzed. Row counts per child table
(sensorcore8, coredetail8, etc) are 126340588 each.
Issue does not occur on 9.4.6 (do not have 9.5 handy to test atm).
--
-- Plan with high estimate
--
EXPLAIN
SELECT core.sensorcoreid
FROM sensor.sensorcore core
JOIN sensor.coredetail cored ON cored.typeid = core.typeid AND
cored.sensorcoreid = core.sensorcoreid
JOIN sensor.corefilldetail corefd ON corefd.typeid = core.typeid AND
corefd.sensorcoreid = core.sensorcoreid
WHERE
core.typeid = 8 AND core.sensorid = 1814821;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=5.62..57175008.37 rows=406484054678631 width=4)
Merge Cond: (corefd.sensorcoreid = cored.sensorcoreid)
-> Merge Append (cost=2.81..28268953.91 rows=126340593 width=8)
Sort Key: corefd.sensorcoreid
-> Index Scan using ix_corefilldetail on corefilldetail corefd
(cost=0.50..3.59 rows=1 width=8)
Filter: (typeid = 8)
-> Index Scan using ix_corefilldetail8 on corefilldetail8 corefd_1
(cost=2.27..21951920.63 rows=126340592 width=8)
Filter: (typeid = 8)
-> Materialize (cost=2.81..16349690.36 rows=125477313 width=12)
-> Nested Loop (cost=2.81..15094917.23 rows=125477313 width=12)
-> Merge Append (cost=2.81..15094701.36 rows=39 width=8)
Sort Key: core.sensorcoreid
-> Index Scan using pk_sensorcore on sensorcore core
(cost=0.50..3.60 rows=1 width=8)
Filter: ((typeid = 8) AND (sensorid = 1814821))
-> Index Scan using sensor_sensorcore8_pkey on
sensorcore8 core_1 (cost=2.27..15094695.77 rows=38 width=8)
Filter: ((typeid = 8) AND (sensorid = 1814821))
-> Append (cost=0.00..5.38 rows=2 width=8)
-> Seq Scan on coredetail cored (cost=0.00..0.00
rows=1 width=8)
Filter: ((typeid = 8) AND (core.sensorcoreid =
sensorcoreid))
-> Index Scan using ix_coredetail8 on coredetail8
cored_1 (cost=2.27..5.38 rows=1 width=8)
Index Cond: (sensorcoreid = core.sensorcoreid)
Filter: (typeid = 8)
--
-- Plan using child partition
--
EXPLAIN
SELECT core.sensorcoreid
FROM sensor.sensorcore8 core
JOIN sensor.coredetail cored ON cored.typeid = core.typeid AND
cored.sensorcoreid = core.sensorcoreid
JOIN sensor.corefilldetail corefd ON corefd.typeid = core.typeid AND
corefd.sensorcoreid = core.sensorcoreid
WHERE
core.typeid = 8 AND core.sensorid = 1814821;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.27..415.58 rows=38 width=4)
Join Filter: (core.sensorcoreid = cored.sensorcoreid)
-> Nested Loop (cost=2.27..227.03 rows=38 width=12)
-> Index Scan using ix_sensorcore8 on sensorcore8 core
(cost=2.27..54.20 rows=38 width=8)
Index Cond: (sensorid = 1814821)
Filter: (typeid = 8)
-> Append (cost=0.00..4.39 rows=2 width=8)
-> Seq Scan on corefilldetail corefd (cost=0.00..0.00
rows=1 width=8)
Filter: ((typeid = 8) AND (core.sensorcoreid =
sensorcoreid))
-> Index Only Scan using
corefilldetail8_typeid_sensorcoreid_idx on corefilldetail8 corefd_1
(cost=2.27..4.39 rows=1 width=8)
Index Cond: ((typeid = 8) AND (sensorcoreid =
core.sensorcoreid))
-> Append (cost=0.00..4.78 rows=2 width=8)
-> Seq Scan on coredetail cored (cost=0.00..0.00 rows=1
width=8)
Filter: ((typeid = 8) AND (corefd.sensorcoreid =
sensorcoreid))
-> Index Scan using ix_coredetail8 on coredetail8 cored_1
(cost=2.27..4.78 rows=1 width=8)
Index Cond: (sensorcoreid = corefd.sensorcoreid)
Filter: (typeid = 8)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
clinton.adams@gmail.com writes:
Row estimates are way off (406484054678631 vs 38) when using master
partition tables. If I change the query to go directly against one child
table, estimates and query time are in line with what I expect.
Those EXPLAINs do look kinda fishy, but with only this much information,
it's unlikely that anyone is going to be able to guess why. A
self-contained example would be much more useful.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Fri, Oct 28, 2016 at 9:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
clinton.adams@gmail.com writes:
Row estimates are way off (406484054678631 vs 38) when using master
partition tables. If I change the query to go directly against one child
table, estimates and query time are in line with what I expect.Those EXPLAINs do look kinda fishy, but with only this much information,
it's unlikely that anyone is going to be able to guess why. A
self-contained example would be much more useful.regards, tom lane
Can confirm that high estimates appear only on 9.6.x, versions 9.4 and 9.5
are fine.
CREATE TABLE core (coreid serial primary key, typeid int NOT NULL, sensorid
int NOT NULL);
CREATE TABLE coredetail (coredetailid serial primary key, coreid int NOT
NULL, typeid int NOT NULL);
CREATE TABLE core20 (CHECK (typeid = 20)) INHERITS (core);
CREATE TABLE coredetail20 (CHECK (typeid = 20)) INHERITS (coredetail);
INSERT INTO core20 (typeid, sensorid) SELECT 20,
generate_series(1,20000000);
INSERT INTO coredetail20 (typeid, coreid) SELECT typeid, coreid FROM core20;
CREATE INDEX ON core(sensorid);
CREATE INDEX ON core20(sensorid);
CREATE INDEX ON coredetail(coreid);
CREATE INDEX ON coredetail20(coreid);
VACUUM ANALYZE core;
VACUUM ANALYZE core20;
VACUUM ANALYZE coredetail;
VACUUM ANALYZE coredetail20;
ALTER TABLE coredetail add FOREIGN KEY (coreid) REFERENCES core;
EXPLAIN
SELECT *
FROM core c
JOIN coredetail cd ON cd.typeid = c.typeid AND c.coreid = cd.coreid
WHERE
c.typeid = 20 AND c.sensorid = 767428;
-- Involving one child table improves things
EXPLAIN
SELECT *
FROM core20 c
JOIN coredetail cd ON cd.typeid = c.typeid AND c.coreid = cd.coreid
WHERE
c.typeid = 20 AND c.sensorid = 767428;
-- Dropping the fkey causes the first query to show a much better row
estimate, in line with 9.4 and 9.5 plans.
ALTER TABLE coredetail DROP CONSTRAINT coredetail_coreid_fkey;
Clinton Adams <clinton.adams@gmail.com> writes:
-- Dropping the fkey causes the first query to show a much better row
estimate, in line with 9.4 and 9.5 plans.
Ah. So this must be something to do with the foreign-key-driven rowcount
estimates we added in 9.6. Will look into it, thanks for the report!
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
I wrote:
Ah. So this must be something to do with the foreign-key-driven rowcount
estimates we added in 9.6. Will look into it, thanks for the report!
I think the answer is that we failed to consider inheritance cases at all
while developing the aforementioned logic. For the moment, I've
band-aided this by disabling the FK estimation logic when either relation
is an inheritance parent. If you need a patch immediately, see
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs