Re: BUG #14404: High row estimates when query uses master inherited tables

On Fri, Oct 28, 2016 at 9:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> clinton(dot)adams(at)gmail(dot)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;