From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: David Fetter <david@fetter.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: psql display of foreign keys
Date: Tue, 4 Dec 2018 12:46:18 -0300
Message-ID: <20181204154618.5diwgpndpyst775g@alvherre.pgsql> (raw)
In-Reply-To: <20181204152330.GH20931@fetter.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, david@fetter.org, tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org
Subject: Re: psql display of foreign keys
In-Reply-To: <20181204154618.5diwgpndpyst775g@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox