patch: optimize information_schema.constraint_column_usage
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t35956psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 28, 2026 at 02:40 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t35956_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t35956_1 && git checkout t35956_1Patchset v1 (message #1) is on t35956_1
Hello hackers,
The view information_schema.constraint_column_usage becomes slow when
the number of columns and constraints raise to substantial values.
This is because of a join condition that allows only join filter to
enforce. The patch is to optimize it.
See many_constraints.sql file attached for a performance test: create
3000 tables with 10 columns and a PK each and select * from the view.
The last statement works for 22 seconds on master branch, 34
milliseconds optimized on my laptop.
Best Regards,
Alexey Bashtanov
The patch applies cleanly. No regression test fails.
On Thu, Feb 2, 2017 at 6:57 PM, Alexey Bashtanov <bashtanov@imap.cc> wrote:
Hello hackers,
The view information_schema.constraint_column_usage becomes slow when the
number of columns and constraints raise to substantial values.
This is because of a join condition that allows only join filter to enforce.
The patch is to optimize it.
Right. Because there are three relations involved in the condition,
it's evaluation is delayed till all three of them are joined, thus
increasing the sizes of minion joins. With this change, the size of
minion joins reduces. Since this is an all INNER join, splitting the
condition should be fine.
See many_constraints.sql file attached for a performance test: create 3000
tables with 10 columns and a PK each and select * from the view.
The last statement works for 22 seconds on master branch, 34 milliseconds
optimized on my laptop.
Yes, that's a lot of improvement. I can reproduce similar results on my laptop.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Please add this patch to the commitfest so that it's not forgotten.
On Fri, Feb 3, 2017 at 6:10 PM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
The patch applies cleanly. No regression test fails.
On Thu, Feb 2, 2017 at 6:57 PM, Alexey Bashtanov <bashtanov@imap.cc> wrote:
Hello hackers,
The view information_schema.constraint_column_usage becomes slow when the
number of columns and constraints raise to substantial values.
This is because of a join condition that allows only join filter to enforce.
The patch is to optimize it.Right. Because there are three relations involved in the condition,
it's evaluation is delayed till all three of them are joined, thus
increasing the sizes of minion joins. With this change, the size of
minion joins reduces. Since this is an all INNER join, splitting the
condition should be fine.See many_constraints.sql file attached for a performance test: create 3000
tables with 10 columns and a PK each and select * from the view.
The last statement works for 22 seconds on master branch, 34 milliseconds
optimized on my laptop.Yes, that's a lot of improvement. I can reproduce similar results on my laptop.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2/2/17 08:27, Alexey Bashtanov wrote:
The view information_schema.constraint_column_usage becomes slow when
the number of columns and constraints raise to substantial values.
This is because of a join condition that allows only join filter to
enforce. The patch is to optimize it.
committed, thanks
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers