From: Tom Lane <tgl@sss.pgh.pa.us>
To: Arturas Mazeika <mazeika@gmail.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
Date: Tue, 28 Sep 2021 10:13:57 -0400
Message-ID: <2835979.1632838437@sss.pgh.pa.us> (raw)
In-Reply-To: <CAAUL=cFEKJ0KJ35vZRj8ROZKJDDduK_D6haokiKfKBmWAz8DUA@mail.gmail.com>
References: <CAAUL=cFcvUo=7b4T-K5PqiqrF6etp59qcgv77DyK2Swa4VhYuQ@mail.gmail.com>
<CAHOFxGpBFcz3YgLjir_zRe1qUb-uUXDV1EzqobeJcyRkOeaF2Q@mail.gmail.com>
<CAAUL=cHctNJ=vT9TG9jXA6b_BZSroVY2TbUBPwMAh7RqnY1g8Q@mail.gmail.com>
<CAHOFxGp8B41x0cKi-9_eEAUXVFK5UQGr-p2ANCUcRn86BU56Pw@mail.gmail.com>
<CAAUL=cFEKJ0KJ35vZRj8ROZKJDDduK_D6haokiKfKBmWAz8DUA@mail.gmail.com>
Arturas Mazeika <mazeika@gmail.com> writes:
> Thanks a lot for having a look at the query once again in more detail. In
> short, you are right, I fired the liquibase scripts and observed the exact
> query that was hanging in pg_stats_activity. The query was:
> SELECT
> FK.TABLE_NAME as "TABLE_NAME"
> , CU.COLUMN_NAME as "COLUMN_NAME"
> , PK.TABLE_NAME as "REFERENCED_TABLE_NAME"
> , PT.COLUMN_NAME as "REFERENCED_COLUMN_NAME"
> , C.CONSTRAINT_NAME as "CONSTRAINT_NAME"
> FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
> INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
> C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
> INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
> C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
> INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME
> = CU.CONSTRAINT_NAME
> INNER JOIN (
> SELECT
> i1.TABLE_NAME
> , i2.COLUMN_NAME
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
> INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
> i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
> WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
> ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE
> lower(FK.TABLE_NAME)='secrole_condcollection'
TBH, before worrying about performance you should be worrying about
correctness. constraint_name alone is not a sufficient join key
for these tables, so who's to say whether you're even getting the
right answers?
Per SQL spec, the join key to use is probably constraint_catalog
plus constraint_schema plus constraint_name. You might say you
don't need to compare constraint_catalog because that's fixed
within any one Postgres database, and that observation would be
correct. But you can't ignore the schema.
What's worse, the SQL-spec join keys are based on the assumption that
constraint names are unique within schemas, which is not enforced in
Postgres. Maybe you're all right here, because you're only looking
at primary key constraints, which are associated with indexes, which
being relations do indeed have unique-within-schema names. But you
still can't ignore the schema.
On the whole I don't think you're buying anything by going through
the SQL-spec information views, because this query is clearly pretty
dependent on Postgres-specific assumptions even if it looks like it's
portable. And you're definitely giving up a lot of performance, since
those views have so many complications from trying to map the spec's
view of whats-a-constraint onto the Postgres objects (not to mention
the spec's arbitrary opinions about which objects you're allowed to
see). This query would be probably be simpler, more correct, and a
lot faster if rewritten to query the Postgres catalogs directly.
regards, tom lane
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mazeika@gmail.com, mlewis@entrata.com
Subject: Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
In-Reply-To: <2835979.1632838437@sss.pgh.pa.us>
* 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