Thread: BUG #14404: High row estimates when query uses master inherited tables
BUG #14404: High row estimates when query uses master inherited tables
From
clinton.adams@gmail.com
Date:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDQwNApMb2dnZWQgYnk6ICAg ICAgICAgIENsaW50b24gQWRhbXMKRW1haWwgYWRkcmVzczogICAgICBjbGlu dG9uLmFkYW1zQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNi4x Ck9wZXJhdGluZyBzeXN0ZW06ICAgY2VudG9zIDcsIDQuMy4wLTEuZWw3LmVs cmVwby54ODZfNjQgIzEgU01QIFR1ZSBOb3YKRGVzY3JpcHRpb246ICAgICAg ICAKClJvdyBlc3RpbWF0ZXMgYXJlIHdheSBvZmYgKDQwNjQ4NDA1NDY3ODYz MSB2cyAzOCkgd2hlbiB1c2luZyBtYXN0ZXIKcGFydGl0aW9uIHRhYmxlcy4g SWYgSSBjaGFuZ2UgdGhlIHF1ZXJ5IHRvIGdvIGRpcmVjdGx5IGFnYWluc3Qg b25lIGNoaWxkCnRhYmxlLCBlc3RpbWF0ZXMgYW5kIHF1ZXJ5IHRpbWUgYXJl IGluIGxpbmUgd2l0aCB3aGF0IEkgZXhwZWN0Lg0KDQpJc3N1ZSBvY2N1cnMg b24gOS42LjEsIGFsbCB0YWJsZXMgYW5hbHl6ZWQuIFJvdyBjb3VudHMgcGVy IGNoaWxkIHRhYmxlCihzZW5zb3Jjb3JlOCwgY29yZWRldGFpbDgsIGV0Yykg YXJlIDEyNjM0MDU4OCBlYWNoLg0KDQpJc3N1ZSBkb2VzIG5vdCBvY2N1ciBv biA5LjQuNiAoZG8gbm90IGhhdmUgOS41IGhhbmR5IHRvIHRlc3QgYXRtKS4N Cg0KLS0NCi0tIFBsYW4gd2l0aCBoaWdoIGVzdGltYXRlDQotLQ0KRVhQTEFJ Tg0KU0VMRUNUIGNvcmUuc2Vuc29yY29yZWlkDQogICBGUk9NIHNlbnNvci5z ZW5zb3Jjb3JlIGNvcmUgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0K ICAgICBKT0lOIHNlbnNvci5jb3JlZGV0YWlsIGNvcmVkIE9OIGNvcmVkLnR5 cGVpZCA9IGNvcmUudHlwZWlkIEFORApjb3JlZC5zZW5zb3Jjb3JlaWQgPSBj b3JlLnNlbnNvcmNvcmVpZA0KICAgICBKT0lOIHNlbnNvci5jb3JlZmlsbGRl dGFpbCBjb3JlZmQgT04gY29yZWZkLnR5cGVpZCA9IGNvcmUudHlwZWlkIEFO RApjb3JlZmQuc2Vuc29yY29yZWlkID0gY29yZS5zZW5zb3Jjb3JlaWQNCiAg V0hFUkUNCmNvcmUudHlwZWlkID0gOCBBTkQgY29yZS5zZW5zb3JpZCA9IDE4 MTQ4MjE7DQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOICAgICAgCiAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgDQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LQ0KIE1lcmdlIEpvaW4gIChjb3N0PTUuNjIuLjU3MTc1MDA4LjM3IHJvd3M9 NDA2NDg0MDU0Njc4NjMxIHdpZHRoPTQpDQogICBNZXJnZSBDb25kOiAoY29y ZWZkLnNlbnNvcmNvcmVpZCA9IGNvcmVkLnNlbnNvcmNvcmVpZCkNCiAgIC0+ ICBNZXJnZSBBcHBlbmQgIChjb3N0PTIuODEuLjI4MjY4OTUzLjkxIHJvd3M9 MTI2MzQwNTkzIHdpZHRoPTgpDQogICAgICAgICBTb3J0IEtleTogY29yZWZk LnNlbnNvcmNvcmVpZA0KICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcg aXhfY29yZWZpbGxkZXRhaWwgb24gY29yZWZpbGxkZXRhaWwgY29yZWZkIAoo Y29zdD0wLjUwLi4zLjU5IHJvd3M9MSB3aWR0aD04KQ0KICAgICAgICAgICAg ICAgRmlsdGVyOiAodHlwZWlkID0gOCkNCiAgICAgICAgIC0+ICBJbmRleCBT Y2FuIHVzaW5nIGl4X2NvcmVmaWxsZGV0YWlsOCBvbiBjb3JlZmlsbGRldGFp bDggY29yZWZkXzEKIChjb3N0PTIuMjcuLjIxOTUxOTIwLjYzIHJvd3M9MTI2 MzQwNTkyIHdpZHRoPTgpDQogICAgICAgICAgICAgICBGaWx0ZXI6ICh0eXBl aWQgPSA4KQ0KICAgLT4gIE1hdGVyaWFsaXplICAoY29zdD0yLjgxLi4xNjM0 OTY5MC4zNiByb3dzPTEyNTQ3NzMxMyB3aWR0aD0xMikNCiAgICAgICAgIC0+ ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9Mi44MS4uMTUwOTQ5MTcuMjMgcm93cz0x MjU0NzczMTMgd2lkdGg9MTIpDQogICAgICAgICAgICAgICAtPiAgTWVyZ2Ug QXBwZW5kICAoY29zdD0yLjgxLi4xNTA5NDcwMS4zNiByb3dzPTM5IHdpZHRo PTgpDQogICAgICAgICAgICAgICAgICAgICBTb3J0IEtleTogY29yZS5zZW5z b3Jjb3JlaWQNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2Fu IHVzaW5nIHBrX3NlbnNvcmNvcmUgb24gc2Vuc29yY29yZSBjb3JlIAooY29z dD0wLjUwLi4zLjYwIHJvd3M9MSB3aWR0aD04KQ0KICAgICAgICAgICAgICAg ICAgICAgICAgICAgRmlsdGVyOiAoKHR5cGVpZCA9IDgpIEFORCAoc2Vuc29y aWQgPSAxODE0ODIxKSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBJbmRl eCBTY2FuIHVzaW5nIHNlbnNvcl9zZW5zb3Jjb3JlOF9wa2V5IG9uCnNlbnNv cmNvcmU4IGNvcmVfMSAgKGNvc3Q9Mi4yNy4uMTUwOTQ2OTUuNzcgcm93cz0z OCB3aWR0aD04KQ0KICAgICAgICAgICAgICAgICAgICAgICAgICAgRmlsdGVy OiAoKHR5cGVpZCA9IDgpIEFORCAoc2Vuc29yaWQgPSAxODE0ODIxKSkNCiAg ICAgICAgICAgICAgIC0+ICBBcHBlbmQgIChjb3N0PTAuMDAuLjUuMzggcm93 cz0yIHdpZHRoPTgpDQogICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNj YW4gb24gY29yZWRldGFpbCBjb3JlZCAgKGNvc3Q9MC4wMC4uMC4wMApyb3dz PTEgd2lkdGg9OCkNCiAgICAgICAgICAgICAgICAgICAgICAgICAgIEZpbHRl cjogKCh0eXBlaWQgPSA4KSBBTkQgKGNvcmUuc2Vuc29yY29yZWlkID0Kc2Vu c29yY29yZWlkKSkNCiAgICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBT Y2FuIHVzaW5nIGl4X2NvcmVkZXRhaWw4IG9uIGNvcmVkZXRhaWw4CmNvcmVk XzEgIChjb3N0PTIuMjcuLjUuMzggcm93cz0xIHdpZHRoPTgpDQogICAgICAg ICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAoc2Vuc29yY29yZWlk ID0gY29yZS5zZW5zb3Jjb3JlaWQpDQogICAgICAgICAgICAgICAgICAgICAg ICAgICBGaWx0ZXI6ICh0eXBlaWQgPSA4KQ0KDQoNCi0tDQotLSBQbGFuIHVz aW5nIGNoaWxkIHBhcnRpdGlvbg0KLS0NCkVYUExBSU4NClNFTEVDVCBjb3Jl LnNlbnNvcmNvcmVpZA0KICAgRlJPTSBzZW5zb3Iuc2Vuc29yY29yZTggY29y ZQ0KICAgICBKT0lOIHNlbnNvci5jb3JlZGV0YWlsIGNvcmVkIE9OIGNvcmVk LnR5cGVpZCA9IGNvcmUudHlwZWlkIEFORApjb3JlZC5zZW5zb3Jjb3JlaWQg PSBjb3JlLnNlbnNvcmNvcmVpZA0KICAgICBKT0lOIHNlbnNvci5jb3JlZmls bGRldGFpbCBjb3JlZmQgT04gY29yZWZkLnR5cGVpZCA9IGNvcmUudHlwZWlk IEFORApjb3JlZmQuc2Vuc29yY29yZWlkID0gY29yZS5zZW5zb3Jjb3JlaWQN CiAgV0hFUkUNCmNvcmUudHlwZWlkID0gOCBBTkQgY29yZS5zZW5zb3JpZCA9 IDE4MTQ4MjE7DQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOCiAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICANCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBOZXN0ZWQgTG9vcCAgKGNv c3Q9Mi4yNy4uNDE1LjU4IHJvd3M9Mzggd2lkdGg9NCkNCiAgIEpvaW4gRmls dGVyOiAoY29yZS5zZW5zb3Jjb3JlaWQgPSBjb3JlZC5zZW5zb3Jjb3JlaWQp DQogICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTIuMjcuLjIyNy4wMyByb3dz PTM4IHdpZHRoPTEyKQ0KICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcg aXhfc2Vuc29yY29yZTggb24gc2Vuc29yY29yZTggY29yZSAKKGNvc3Q9Mi4y Ny4uNTQuMjAgcm93cz0zOCB3aWR0aD04KQ0KICAgICAgICAgICAgICAgSW5k ZXggQ29uZDogKHNlbnNvcmlkID0gMTgxNDgyMSkNCiAgICAgICAgICAgICAg IEZpbHRlcjogKHR5cGVpZCA9IDgpDQogICAgICAgICAtPiAgQXBwZW5kICAo Y29zdD0wLjAwLi40LjM5IHJvd3M9MiB3aWR0aD04KQ0KICAgICAgICAgICAg ICAgLT4gIFNlcSBTY2FuIG9uIGNvcmVmaWxsZGV0YWlsIGNvcmVmZCAgKGNv c3Q9MC4wMC4uMC4wMApyb3dzPTEgd2lkdGg9OCkNCiAgICAgICAgICAgICAg ICAgICAgIEZpbHRlcjogKCh0eXBlaWQgPSA4KSBBTkQgKGNvcmUuc2Vuc29y Y29yZWlkID0Kc2Vuc29yY29yZWlkKSkNCiAgICAgICAgICAgICAgIC0+ICBJ bmRleCBPbmx5IFNjYW4gdXNpbmcKY29yZWZpbGxkZXRhaWw4X3R5cGVpZF9z ZW5zb3Jjb3JlaWRfaWR4IG9uIGNvcmVmaWxsZGV0YWlsOCBjb3JlZmRfMSAK KGNvc3Q9Mi4yNy4uNC4zOSByb3dzPTEgd2lkdGg9OCkNCiAgICAgICAgICAg ICAgICAgICAgIEluZGV4IENvbmQ6ICgodHlwZWlkID0gOCkgQU5EIChzZW5z b3Jjb3JlaWQgPQpjb3JlLnNlbnNvcmNvcmVpZCkpDQogICAtPiAgQXBwZW5k ICAoY29zdD0wLjAwLi40Ljc4IHJvd3M9MiB3aWR0aD04KQ0KICAgICAgICAg LT4gIFNlcSBTY2FuIG9uIGNvcmVkZXRhaWwgY29yZWQgIChjb3N0PTAuMDAu LjAuMDAgcm93cz0xCndpZHRoPTgpDQogICAgICAgICAgICAgICBGaWx0ZXI6 ICgodHlwZWlkID0gOCkgQU5EIChjb3JlZmQuc2Vuc29yY29yZWlkID0Kc2Vu c29yY29yZWlkKSkNCiAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIGl4 X2NvcmVkZXRhaWw4IG9uIGNvcmVkZXRhaWw4IGNvcmVkXzEgCihjb3N0PTIu MjcuLjQuNzggcm93cz0xIHdpZHRoPTgpDQogICAgICAgICAgICAgICBJbmRl eCBDb25kOiAoc2Vuc29yY29yZWlkID0gY29yZWZkLnNlbnNvcmNvcmVpZCkN CiAgICAgICAgICAgICAgIEZpbHRlcjogKHR5cGVpZCA9IDgpDQoKCg==
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
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
I wrote: > Ah. So this must be something to do with the foreign-key-driven rowcoun= t > 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 https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dcommitdiff;h=3D2= 3c6c437f98c996092c0adfad6152d9cc699c8b0 regards, tom lane