From: Antonin Houska <ah@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Robert Haas <robertmhaas@gmail.com>,
    Andres Freund <andres@anarazel.de>,
    Alvaro Herrera <alvherre@2ndquadrant.com>,
    Corey Huinker <corey.huinker@gmail.com>,
    Pavel Stehule <pavel.stehule@gmail.com>,
    PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: More efficient RI checks - take 2
In-reply-to: <6442.1587595207@sss.pgh.pa.us>
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>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
   message dated "Wed, 22 Apr 2020 18:40:07 -0400."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <28825.1587618482.1@antos>
Date: Thu, 23 Apr 2020 07:08:02 +0200
Message-ID: <28826.1587618482@antos>
Precedence: bulk

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
> > Right -- the idea I was talking about was to create a Plan tree
> > without using the main planner. So it wouldn't bother costing an index
> > scan on each index, and a sequential scan, on the target table - it
> > would just make an index scan plan, or maybe an index path that it
> > would then convert to an index plan. Or something like that.
> 
> Consing up a Path tree and then letting create_plan() make it into
> an executable plan might not be a terrible idea.  There's a whole
> boatload of finicky details that you could avoid that way, like
> everything in setrefs.c.
> 
> 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?

I'm concerned about that too. With my patch the checks become a bit slower if
only a single row is processed. The problem seems to be that the planner is
not entirely convinced about that the number of input rows, so it can still
build a plan that expects many rows. For example (as I mentioned elsewhere in
the thread), a hash join where the hash table only contains one tuple. Or
similarly a sort node for a single input tuple.

-- 
Antonin Houska
Web: https://www.cybertec-postgresql.com