Date: Thu, 23 Apr 2020 08:40:47 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Andres Freund <andres@anarazel.de>,
	Alvaro Herrera <alvherre@2ndquadrant.com>,
	Corey Huinker <corey.huinker@gmail.com>,
	Antonin Houska <ah@cybertec.at>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: More efficient RI checks - take 2
Message-ID: <20200423124046.GD13712@tamriel.snowman.net>
References: <20200422154231.6shz4kdor4yb5w5b@alap3.anarazel.de>
 <20200422171806.GA12435@alvherre.pgsql>
 <CA+TgmoZF_fkSYTcY7=yiRr7MxC=yt1VoZPMcYfwsRYw6TusEmA@mail.gmail.com>
 <20200422183600.tpl5745dfbnozi6t@alap3.anarazel.de>
 <CA+Tgmobb2_sm8PZ16_dqevUktcjT8OnZ5xvv=AFtxWWjf28VxA@mail.gmail.com>
 <6442.1587595207@sss.pgh.pa.us>
 <CA+TgmoYSxummxtJ1XJecnR-CuwS8gaMnoC02WC2BKd6+67_R7g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Ar40rvwHfUXOgY8v"
Content-Disposition: inline
In-Reply-To: 
 <CA+TgmoYSxummxtJ1XJecnR-CuwS8gaMnoC02WC2BKd6+67_R7g@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--Ar40rvwHfUXOgY8v
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, Apr 22, 2020 at 6:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > But it's not entirely clear to me that we know the best plan for a
> > statement-level RI action with sufficient certainty to go that way.
> > Is it really the case that the plan would not vary based on how
> > many tuples there are to check, for example?  If we *do* know
> > exactly what should happen, I'd tend to lean towards Andres'
> > idea that we shouldn't be using the executor at all, but just
> > hard-wiring stuff at the level of "do these table scans".
>=20
> Well, I guess I'd naively think we want an index scan on a plain
> table. It is barely possible that in some corner case a sequential
> scan would be faster, but could it be enough faster to save the cost
> of planning? I doubt it, but I just work here.
>=20
> On a partitioning hierarchy we want to figure out which partition is
> relevant for the value we're trying to find, and then scan that one.
>=20
> I'm not sure there are any other cases. We have to have a UNIQUE
> constraint or we can't be referencing this target table. So it can't
> be a plain inheritance hierarchy, nor (I think) a foreign table.

In the cases where we have a UNIQUE constraint, and therefore a clear
index to use, I tend to agree that we should just be getting to it and
avoiding the planner/executor, as Andres suggest.

I'm not super thrilled about the idea of throwing an ERROR when we
haven't got an index to use though, and we don't require an index on the
referring side, meaning that, with such a change, a DELETE or UPDATE on
the referred table with an ON CASCADE FK will just start throwing
errors.  That's not terribly friendly, even if it's not really best
practice to not have an index to help with those cases.

I'd hope that we would at least teach pg_upgrade to look for such cases
and throw errors (though maybe that could be downgraded to a WARNING
with a flag..?) if it finds any when upgrading, so that users don't
upgrade and then suddenly start getting errors for simple statements
that used to work just fine.

> > On the whole I still think that generating a Query tree and then
> > letting the planner do its thing might be the best approach.
>=20
> Maybe, but it seems awfully heavy-weight. Once you go into the planner
> it's pretty hard to avoid considering indexes we don't care about,
> bitmap scans we don't care about, a sequential scan we don't care
> about, etc.  You'd certainly save something just from avoiding
> parsing, but planning's pretty expensive too.

Agreed.

Thanks,

Stephen

--Ar40rvwHfUXOgY8v
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJeoYzOAAoJEO1sijiDR2RVwaUP/ijZF5v4XzaIlaVM2RJtvYDO
e3u2UMrQzTmLXzOKtIyfBV5LW9NUHN/pId1RjpzMBRZqXnS2wdlG8FoKln0kEZBd
V8XpZ1z7RNCRY6xP6rk+oi5pJclhtlkvEp0dNcHiElXfs/hEkzC24Ke3LDFyjdwG
7FiY1AISB1UKXNVB0CvEruKjv5UkPeB4USRjKl7CiDlBWjmoAUJ0Mk7NNF5bk4Gq
BHFUemudFuRuC+OhEu4raIcaK1S1WRiJr8aGjlyZXWUApgOtKijFrzgVN4A4fbYY
smTSw07mtVrI/kAJy16QHiKLpGgjDlk0KYfPByJYIA6tYf4ssRssr3csuU1FqbhH
4pY6+Jo57tabesxgGdjbHMVyd8VA7E64O1ia7xNTLUAW9sOsWJG3ip94Rlyq22ki
e8uBkHsFzLEWw33G2sDwavsqmAR+1gREb5vNAf1yXTFNA9Rbaa0fAQIMaPRjBBvj
9OhvfK3aFjzEK2ckazPUV1V1aAwpBqfjGHGGGnIBck/+LPvITtGUR6cxb1Dv6Cvy
pb98LsihUGkJgwSTAB+ZjNJPdNOyLmj9rEJiEBaKfvZUisX3VZ21MFFpvr7kEo3x
SNZNQ4Uh5X+ciaDVLXhSJJY/YBegimLPshvy/mIiLrSebe3woLfkQixR6M2k7RXM
8OZ41B6Sz4tYTiG1B0Wn
=bJtN
-----END PGP SIGNATURE-----

--Ar40rvwHfUXOgY8v--