Date: Mon, 23 Mar 2020 13:50:59 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: weird hash plan cost, starting with pg10
Message-ID: <20200323165059.GA24950@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

Hello

While messing with EXPLAIN on a query emitted by pg_dump, I noticed that
current Postgres 10 emits weird bucket/batch/memory values for certain
hash nodes:

                         ->  Hash  (cost=3D0.11..0.11 rows=3D10 width=3D12)=
 (actual time=3D0.002..0.002 rows=3D1 loops=3D8)
                               Buckets: 2139062143  Batches: 2139062143  Me=
mory Usage: 8971876904722400kB
                               ->  Function Scan on unnest init_1  (cost=3D=
0.01..0.11 rows=3D10 width=3D12) (actual time=3D0.001..0.001 rows=3D1 loops=
=3D8)

It shows normal values in 9.6.

The complete query is:

SELECT c.tableoid, c.oid, c.relname, (SELECT pg_catalog.array_agg(acl ORDER=
 BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(c.relacl=
,pg_catalog.acldefault(CASE WHEN c.relkind =3D 'S' THEN 's' ELSE 'r' END::"=
char",c.relowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( S=
ELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault=
(CASE WHEN c.relkind =3D 'S' THEN 's' ELSE 'r' END::"char",c.relowner))) AS=
 init(init_acl) WHERE acl =3D init_acl)) as foo) AS relacl, (SELECT pg_cata=
log.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.u=
nnest(coalesce(pip.initprivs,pg_catalog.acldefault(CASE WHEN c.relkind =3D =
'S' THEN 's' ELSE 'r' END::"char",c.relowner))) WITH ORDINALITY AS initp(ac=
l,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(c.rela=
cl,pg_catalog.acldefault(CASE WHEN c.relkind =3D 'S' THEN 's' ELSE 'r' END:=
:"char",c.relowner))) AS permp(orig_acl) WHERE acl =3D orig_acl)) as foo) a=
s rrelacl, NULL AS initrelacl, NULL as initrrelacl, c.relkind, c.relnamespa=
ce, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =3D c.relowner) AS r=
olname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, 'f'::b=
ool AS relhasoids, c.relrowsecurity, c.relforcerowsecurity, c.relfrozenxid,=
 c.relminmxid, tc.oid AS toid, tc.relfrozenxid AS tfrozenxid, tc.relminmxid=
 AS tminmxid, c.relpersistence, c.relispopulated, c.relreplident, c.relpage=
s, am.amname, CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regty=
pe ELSE NULL END AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS o=
wning_col, (SELECT spcname FROM pg_tablespace t WHERE t.oid =3D c.reltables=
pace) AS reltablespace, array_remove(array_remove(c.reloptions,'check_optio=
n=3Dlocal'),'check_option=3Dcascaded') AS reloptions, CASE WHEN 'check_opti=
on=3Dlocal' =3D ANY (c.reloptions) THEN 'LOCAL'::text WHEN 'check_option=3D=
cascaded' =3D ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS che=
ckoption, tc.reloptions AS toast_reloptions, c.relkind =3D 'S' AND EXISTS (=
SELECT 1 FROM pg_depend WHERE classid =3D 'pg_class'::regclass AND objid =
=3D c.oid AND objsubid =3D 0 AND refclassid =3D 'pg_class'::regclass AND de=
ptype =3D 'i') AS is_identity_sequence, EXISTS (SELECT 1 FROM pg_attribute =
at LEFT JOIN pg_init_privs pip ON (c.oid =3D pip.objoid AND pip.classoid =
=3D 'pg_class'::regclass AND pip.objsubid =3D at.attnum)WHERE at.attrelid =
=3D c.oid AND ((SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELEC=
T acl, row_n FROM pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefaul=
t('c',c.relowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( S=
ELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault=
('c',c.relowner))) AS init(init_acl) WHERE acl =3D init_acl)) as foo) IS NO=
T NULL OR (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl=
, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault=
('c',c.relowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( S=
ELECT 1 FROM pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefault('c'=
,c.relowner))) AS permp(orig_acl) WHERE acl =3D orig_acl)) as foo) IS NOT N=
ULL OR NULL IS NOT NULL OR NULL IS NOT NULL))AS changed_acl, pg_get_partkey=
def(c.oid) AS partkeydef, c.relispartition AS ispartition, pg_get_expr(c.re=
lpartbound, c.oid) AS partbound FROM pg_class c LEFT JOIN pg_depend d ON (c=
=2Erelkind =3D 'S' AND d.classid =3D c.tableoid AND d.objid =3D c.oid AND d=
=2Eobjsubid =3D 0 AND d.refclassid =3D c.tableoid AND d.deptype IN ('a', 'i=
')) LEFT JOIN pg_class tc ON (c.reltoastrelid =3D tc.oid AND c.relkind <> '=
p') LEFT JOIN pg_am am ON (c.relam =3D am.oid) LEFT JOIN pg_init_privs pip =
ON (c.oid =3D pip.objoid AND pip.classoid =3D 'pg_class'::regclass AND pip.=
objsubid =3D 0) WHERE c.relkind in ('r', 'S', 'v', 'c', 'm', 'f', 'p') ORDE=
R BY c.oid

I'm not looking into this right now.  If somebody is bored in
quarantine, they might have a good time bisecting this.

--=20
=C1lvaro Herrera