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

Commitdec1034

Browse files
committed
Improve initdb's query for generating default descriptions a little.
While poking into initdb's performance, I noticed that this querywasn't being done very intelligently. By forcing it to executeobj_description() for each pg_proc/pg_operator join row, we wereessentially setting up a nestloop join to pg_description, whichis not a bright query plan when there are hundreds of outer rows.Convert the check for a "deprecated" operator into a NOT EXISTSso that it can be done as a hashed antijoin. On my workstationthis reduces the time for this query from ~ 35ms to ~ 10ms.Which is not a huge win, but it adds up over buildfarm runs.In passing, insert forced query breaks (\n\n, in single-user mode)after each SQL-query file that initdb sources, and after somerelatively new queries in setup_privileges(). This doesn't makea lot of difference normally, but it will result in briefer, sanererror messages if anything goes wrong.
1 parent831f5d1 commitdec1034

File tree

1 file changed

+21
-14
lines changed

1 file changed

+21
-14
lines changed

‎src/bin/initdb/initdb.c

Lines changed: 21 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1669,6 +1669,8 @@ setup_sysviews(FILE *cmdfd)
16691669
free(*line);
16701670
}
16711671

1672+
PG_CMD_PUTS("\n\n");
1673+
16721674
free(sysviews_setup);
16731675
}
16741676

@@ -1707,16 +1709,17 @@ setup_description(FILE *cmdfd)
17071709

17081710
/* Create default descriptions for operator implementation functions */
17091711
PG_CMD_PUTS("WITH funcdescs AS ( "
1710-
"SELECT p.oid as p_oid, oprname, "
1711-
"coalesce(obj_description(o.oid, 'pg_operator'),'') as opdesc "
1712+
"SELECT p.oid as p_oid, o.oid as o_oid, oprname "
17121713
"FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid ) "
17131714
"INSERT INTO pg_description "
17141715
" SELECT p_oid, 'pg_proc'::regclass, 0, "
17151716
" 'implementation of ' || oprname || ' operator' "
17161717
" FROM funcdescs "
1717-
" WHERE opdesc NOT LIKE 'deprecated%' AND "
1718-
" NOT EXISTS (SELECT 1 FROM pg_description "
1719-
" WHERE objoid = p_oid AND classoid = 'pg_proc'::regclass);\n\n");
1718+
" WHERE NOT EXISTS (SELECT 1 FROM pg_description "
1719+
" WHERE objoid = p_oid AND classoid = 'pg_proc'::regclass) "
1720+
" AND NOT EXISTS (SELECT 1 FROM pg_description "
1721+
" WHERE objoid = o_oid AND classoid = 'pg_operator'::regclass"
1722+
" AND description LIKE 'deprecated%');\n\n");
17201723

17211724
/*
17221725
* Even though the tables are temp, drop them explicitly so they don't get
@@ -1780,6 +1783,8 @@ setup_dictionary(FILE *cmdfd)
17801783
free(*line);
17811784
}
17821785

1786+
PG_CMD_PUTS("\n\n");
1787+
17831788
free(conv_lines);
17841789
}
17851790

@@ -1836,7 +1841,7 @@ setup_privileges(FILE *cmdfd)
18361841
" relacl IS NOT NULL"
18371842
" AND relkind IN ("CppAsString2(RELKIND_RELATION) ", "
18381843
CppAsString2(RELKIND_VIEW)", "CppAsString2(RELKIND_MATVIEW)", "
1839-
CppAsString2(RELKIND_SEQUENCE)");",
1844+
CppAsString2(RELKIND_SEQUENCE)");\n\n",
18401845
"INSERT INTO pg_init_privs "
18411846
" (objoid, classoid, objsubid, initprivs, privtype)"
18421847
" SELECT"
@@ -1852,7 +1857,7 @@ setup_privileges(FILE *cmdfd)
18521857
" pg_attribute.attacl IS NOT NULL"
18531858
" AND pg_class.relkind IN ("CppAsString2(RELKIND_RELATION) ", "
18541859
CppAsString2(RELKIND_VIEW)", "CppAsString2(RELKIND_MATVIEW)", "
1855-
CppAsString2(RELKIND_SEQUENCE)");",
1860+
CppAsString2(RELKIND_SEQUENCE)");\n\n",
18561861
"INSERT INTO pg_init_privs "
18571862
" (objoid, classoid, objsubid, initprivs, privtype)"
18581863
" SELECT"
@@ -1864,7 +1869,7 @@ setup_privileges(FILE *cmdfd)
18641869
" FROM"
18651870
" pg_proc"
18661871
" WHERE"
1867-
" proacl IS NOT NULL;",
1872+
" proacl IS NOT NULL;\n\n",
18681873
"INSERT INTO pg_init_privs "
18691874
" (objoid, classoid, objsubid, initprivs, privtype)"
18701875
" SELECT"
@@ -1876,7 +1881,7 @@ setup_privileges(FILE *cmdfd)
18761881
" FROM"
18771882
" pg_type"
18781883
" WHERE"
1879-
" typacl IS NOT NULL;",
1884+
" typacl IS NOT NULL;\n\n",
18801885
"INSERT INTO pg_init_privs "
18811886
" (objoid, classoid, objsubid, initprivs, privtype)"
18821887
" SELECT"
@@ -1888,7 +1893,7 @@ setup_privileges(FILE *cmdfd)
18881893
" FROM"
18891894
" pg_language"
18901895
" WHERE"
1891-
" lanacl IS NOT NULL;",
1896+
" lanacl IS NOT NULL;\n\n",
18921897
"INSERT INTO pg_init_privs "
18931898
" (objoid, classoid, objsubid, initprivs, privtype)"
18941899
" SELECT"
@@ -1901,7 +1906,7 @@ setup_privileges(FILE *cmdfd)
19011906
" FROM"
19021907
" pg_largeobject_metadata"
19031908
" WHERE"
1904-
" lomacl IS NOT NULL;",
1909+
" lomacl IS NOT NULL;\n\n",
19051910
"INSERT INTO pg_init_privs "
19061911
" (objoid, classoid, objsubid, initprivs, privtype)"
19071912
" SELECT"
@@ -1913,7 +1918,7 @@ setup_privileges(FILE *cmdfd)
19131918
" FROM"
19141919
" pg_namespace"
19151920
" WHERE"
1916-
" nspacl IS NOT NULL;",
1921+
" nspacl IS NOT NULL;\n\n",
19171922
"INSERT INTO pg_init_privs "
19181923
" (objoid, classoid, objsubid, initprivs, privtype)"
19191924
" SELECT"
@@ -1926,7 +1931,7 @@ setup_privileges(FILE *cmdfd)
19261931
" FROM"
19271932
" pg_foreign_data_wrapper"
19281933
" WHERE"
1929-
" fdwacl IS NOT NULL;",
1934+
" fdwacl IS NOT NULL;\n\n",
19301935
"INSERT INTO pg_init_privs "
19311936
" (objoid, classoid, objsubid, initprivs, privtype)"
19321937
" SELECT"
@@ -1939,7 +1944,7 @@ setup_privileges(FILE *cmdfd)
19391944
" FROM"
19401945
" pg_foreign_server"
19411946
" WHERE"
1942-
" srvacl IS NOT NULL;",
1947+
" srvacl IS NOT NULL;\n\n",
19431948
NULL
19441949
};
19451950

@@ -1994,6 +1999,8 @@ setup_schema(FILE *cmdfd)
19941999
free(*line);
19952000
}
19962001

2002+
PG_CMD_PUTS("\n\n");
2003+
19972004
free(lines);
19982005

19992006
PG_CMD_PRINTF1("UPDATE information_schema.sql_implementation_info "

0 commit comments

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