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;
