I poked into the odd behavior reported in bug #15251:
https://www.postgresql.org/message-id/152967245839.1266.6939666809369185595@wrigleys.postgresql.org
The reason for the poor plan chosen when the caller hasn't got select
privilege on the child table is that statistic_proc_security_check()
decides not to allow use of the stats for the child table. There are
two functions for which it decides that, because they aren't leakproof:
textregexeq() and btint4cmp(). Now, it's probably necessary that we
consider textregexeq() leaky, since it might spit up errors about its
pattern argument. But btint4cmp? It seems pretty silly that the
integer comparison operators are marked leakproof while their underlying
comparison function isn't.
(The reason we're hitting this is that calc_arraycontsel() finds the
datatype's default btree comparison function and tries to use that
to estimate selectivity. While marking btint4cmp leakproof doesn't
completely fix the misestimation, it goes a long way in this example.)
I propose to run through the system operator classes, find any for which
the comparison function isn't marked leakproof but the operators are,
and fix them. This is clearly appropriate for HEAD and maybe it's not
too late to force an initdb for v11 --- thoughts?
Another question that could be raised is why we are refusing to use
stats for a child table when the caller has select on the parent.
It's completely trivial to extract data from a child table if you
have select on the parent, so it seems like we are checking the
wrong table's privileges.
regards, tom lane
