Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite6555b4

Browse files
committed
Simplify a couple of pg_dump and psql \d queries about index constraints
by joining to pg_constraint.conindid, instead of the former technique ofjoining indirectly through pg_depend. This is much more straightforwardand probably faster as well. I had originally desisted from changing thesequeries when conindid was added because I was worried about losingperformance, but if we join on conrelid as well as conindid then the indexon conrelid can be used when pg_constraint is large.
1 parent4df5c6c commite6555b4

File tree

2 files changed

+30
-36
lines changed

2 files changed

+30
-36
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,7 @@
2525
*http://archives.postgresql.org/pgsql-bugs/2010-02/msg00187.php
2626
*
2727
* IDENTIFICATION
28-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.577 2010/03/03 23:38:44 momjian Exp $
28+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.578 2010/03/11 04:36:43 tgl Exp $
2929
*
3030
*-------------------------------------------------------------------------
3131
*/
@@ -3956,6 +3956,11 @@ getIndexes(TableInfo tblinfo[], int numTables)
39563956
* that is related by an internal dependency link to the index. If we
39573957
* find one, create a CONSTRAINT entry linked to the INDEX entry. We
39583958
* assume an index won't have more than one internal dependency.
3959+
*
3960+
* As of 9.0 we don't need to look at pg_depend but can check for
3961+
* a match to pg_constraint.conindid. The check on conrelid is
3962+
* redundant but useful because that column is indexed while conindid
3963+
* is not.
39593964
*/
39603965
resetPQExpBuffer(query);
39613966
if (g_fout->remoteVersion >=90000)
@@ -3975,13 +3980,10 @@ getIndexes(TableInfo tblinfo[], int numTables)
39753980
"array_to_string(t.reloptions, ', ') AS options "
39763981
"FROM pg_catalog.pg_index i "
39773982
"JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
3978-
"LEFT JOIN pg_catalog.pg_depend d "
3979-
"ON (d.classid = t.tableoid "
3980-
"AND d.objid = t.oid "
3981-
"AND d.deptype = 'i') "
39823983
"LEFT JOIN pg_catalog.pg_constraint c "
3983-
"ON (d.refclassid = c.tableoid "
3984-
"AND d.refobjid = c.oid) "
3984+
"ON (i.indrelid = c.conrelid AND "
3985+
"i.indexrelid = c.conindid AND "
3986+
"c.contype IN ('p','u','x')) "
39853987
"WHERE i.indrelid = '%u'::pg_catalog.oid "
39863988
"ORDER BY indexname",
39873989
tbinfo->dobj.catId.oid);

‎src/bin/psql/describe.c

Lines changed: 21 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
* Copyright (c) 2000-2010, PostgreSQL Global Development Group
1010
*
11-
* $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.239 2010/03/01 20:55:45 heikki Exp $
11+
* $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.240 2010/03/11 04:36:43 tgl Exp $
1212
*/
1313
#include"postgres_fe.h"
1414

@@ -1417,21 +1417,17 @@ describeOneTableDetails(const char *schemaname,
14171417
if (pset.sversion >=90000)
14181418
appendPQExpBuffer(&buf,
14191419
" (NOT i.indimmediate) AND "
1420-
"EXISTS (SELECT 1 FROM pg_catalog.pg_depend d, "
1421-
"pg_catalog.pg_constraint con WHERE "
1422-
"d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass AND "
1423-
"d.objid = i.indexrelid AND "
1424-
"d.refclassid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND "
1425-
"d.refobjid = con.oid AND d.deptype = 'i' AND "
1426-
"con.condeferrable) AS condeferrable,\n"
1420+
"EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1421+
"WHERE conrelid = i.indrelid AND "
1422+
"conindid = i.indexrelid AND "
1423+
"contype IN ('p','u','x') AND "
1424+
"condeferrable) AS condeferrable,\n"
14271425
" (NOT i.indimmediate) AND "
1428-
"EXISTS (SELECT 1 FROM pg_catalog.pg_depend d, "
1429-
"pg_catalog.pg_constraint con WHERE "
1430-
"d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass AND "
1431-
"d.objid = i.indexrelid AND "
1432-
"d.refclassid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND "
1433-
"d.refobjid = con.oid AND d.deptype = 'i' AND "
1434-
"con.condeferred) AS condeferred,\n");
1426+
"EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1427+
"WHERE conrelid = i.indrelid AND "
1428+
"conindid = i.indexrelid AND "
1429+
"contype IN ('p','u','x') AND "
1430+
"condeferred) AS condeferred,\n");
14351431
else
14361432
appendPQExpBuffer(&buf,
14371433
" false AS condeferrable, false AS condeferred,\n");
@@ -1553,21 +1549,17 @@ describeOneTableDetails(const char *schemaname,
15531549
if (pset.sversion >=90000)
15541550
appendPQExpBuffer(&buf,
15551551
",\n (NOT i.indimmediate) AND "
1556-
"EXISTS (SELECT 1 FROM pg_catalog.pg_depend d, "
1557-
"pg_catalog.pg_constraint con WHERE "
1558-
"d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass AND "
1559-
"d.objid = i.indexrelid AND "
1560-
"d.refclassid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND "
1561-
"d.refobjid = con.oid AND d.deptype = 'i' AND "
1562-
"con.condeferrable) AS condeferrable"
1552+
"EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1553+
"WHERE conrelid = i.indrelid AND "
1554+
"conindid = i.indexrelid AND "
1555+
"contype IN ('p','u','x') AND "
1556+
"condeferrable) AS condeferrable"
15631557
",\n (NOT i.indimmediate) AND "
1564-
"EXISTS (SELECT 1 FROM pg_catalog.pg_depend d, "
1565-
"pg_catalog.pg_constraint con WHERE "
1566-
"d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass AND "
1567-
"d.objid = i.indexrelid AND "
1568-
"d.refclassid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND "
1569-
"d.refobjid = con.oid AND d.deptype = 'i' AND "
1570-
"con.condeferred) AS condeferred");
1558+
"EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1559+
"WHERE conrelid = i.indrelid AND "
1560+
"conindid = i.indexrelid AND "
1561+
"contype IN ('p','u','x') AND "
1562+
"condeferred) AS condeferred");
15711563
else
15721564
appendPQExpBuffer(&buf,", false AS condeferrable, false AS condeferred");
15731565
if (pset.sversion >=80000)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp