Date: Tue, 4 Dec 2018 12:46:18 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: David Fetter <david@fetter.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: psql display of foreign keys
Message-ID: <20181204154618.5diwgpndpyst775g@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20181204152330.GH20931@fetter.org>
User-Agent: NeoMutt/20180716
Precedence: bulk

On 2018-Dec-04, David Fetter wrote:

> On Tue, Dec 04, 2018 at 10:00:00AM -0500, Tom Lane wrote:

> > That's probably a win performance-wise anyway, as I have no doubt
> > that the performance of this query is awful compared to what it
> > replaces, so we don't really want to use it if we don't have to.

Sure thing.

Fixed the easy one.  On to the other one ...

> Do you have cases where we should be measuring performance dips?
> Also, is there something about  about indexes involved in this query
> or WITH RECURSIVE itself that's pessimizing performance, generally?

Note that there are two queries being changed in this patch, one for
each side of any foreign key.  They start with either a lookup on
conrelid or confrelid; only one of those columns has an index (so
priming the CTE is a little slow for the confrelid one, if your
pg_constraint is bloated).  But after that the CTE iterates on the OID
column, which is indexed, so it should be quick enough.

This is the conrelid plan:
 Sort  (cost=1605.38..1605.39 rows=1 width=101)
   Sort Key: ((constraints.conrelid = '311099'::oid)) DESC, constraints.conname
   CTE constraints
     ->  Recursive Union  (cost=0.29..1600.82 rows=202 width=76)
           ->  Index Scan using pg_constraint_conrelid_contypid_conname_index on pg_constraint  (cost=0.29..11.77 rows=2 width=76)
                 Index Cond: (conrelid = '311099'::oid)
                 Filter: (contype = 'f'::"char")
           ->  Nested Loop  (cost=0.29..158.50 rows=20 width=76)
                 ->  WorkTable Scan on constraints constraints_1  (cost=0.00..0.40 rows=20 width=4)
                 ->  Index Scan using pg_constraint_oid_index on pg_constraint pc  (cost=0.29..7.90 rows=1 width=76)
                       Index Cond: (oid = constraints_1.parent)
   ->  CTE Scan on constraints  (cost=0.00..4.55 rows=1 width=101)
         Filter: (parent = '0'::oid)

This is the confrelid plan:
 Sort  (cost=1793.40..1793.40 rows=1 width=100)
   Sort Key: constraints.conname
   CTE constraints
     ->  Recursive Union  (cost=0.00..1791.11 rows=101 width=80)
           ->  Seq Scan on pg_constraint  (cost=0.00..956.59 rows=1 width=80)
                 Filter: ((contype = 'f'::"char") AND (confrelid = '311099'::oid))
           ->  Nested Loop  (cost=0.29..83.25 rows=10 width=80)
                 ->  WorkTable Scan on constraints constraints_1  (cost=0.00..0.20 rows=10 width=4)
                 ->  Index Scan using pg_constraint_oid_index on pg_constraint pc  (cost=0.29..8.30 rows=1 width=80)
                       Index Cond: (oid = constraints_1.parent)
   ->  CTE Scan on constraints  (cost=0.00..2.27 rows=1 width=100)
         Filter: (parent = '0'::oid)

Of course, the original queries did the same thing (lookup via unindexed
confrelid) and nobody has complained about that yet.  Then again, the
difference between a query taking 0.1 ms (the original query on
conrelid, without recursive CTE) and one that takes 6ms (recursive one
on confrelid) is not noticeable to humans anyway; it's not like this is
a hot path.

In any case, if anyone can think of another method to obtain the topmost
constraint of a hierarchy involving the current table (not involving a
recursive CTE, or maybe with a better one), I'm all ears.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services