Re: psql display of foreign keys

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