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

Commit166f69f

Browse files
committed
Fix O(N^2) performance issue in pg_publication_tables view.
The original coding of this view relied on a correlated IN sub-query.Our planner is not very bright about correlated sub-queries, and evenif it were, there's no way for it to know that the output ofpg_get_publication_tables() is duplicate-free, making the de-duplicatingsemantics of IN unnecessary. Hence, rewrite as a LATERAL sub-query.This provides circa 100X speedup for me with a few hundred publishedtables (the whole regression database), and things would degrade asroughly O(published_relations * all_relations) beyond that.Because the rules.out expected output changes, force a catversion bump.Ordinarily we might not want to do that post-beta1; but we already knowwe'll be doing a catversion bump before beta2 to fix pg_statistic_extissues, so it's pretty much free to fix it now instead of waiting for v13.Per report and fix suggestion from PegoraroF10.Discussion:https://postgr.es/m/1551385426763-0.post@n3.nabble.com
1 parent728840f commit166f69f

File tree

3 files changed

+7
-6
lines changed

3 files changed

+7
-6
lines changed

‎src/backend/catalog/system_views.sql

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -258,9 +258,10 @@ CREATE VIEW pg_publication_tables AS
258258
P.pubnameAS pubname,
259259
N.nspnameAS schemaname,
260260
C.relnameAS tablename
261-
FROM pg_publication P, pg_class C
262-
JOIN pg_namespace NON (N.oid=C.relnamespace)
263-
WHEREC.oidIN (SELECT relidFROM pg_get_publication_tables(P.pubname));
261+
FROM pg_publication P,
262+
LATERAL pg_get_publication_tables(P.pubname) GPT,
263+
pg_class CJOIN pg_namespace NON (N.oid=C.relnamespace)
264+
WHEREC.oid=GPT.relid;
264265

265266
CREATEVIEWpg_locksAS
266267
SELECT*FROM pg_lock_status()AS L;

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201905141
56+
#defineCATALOG_VERSION_NO201905221
5757

5858
#endif

‎src/test/regress/expected/rules.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1441,10 +1441,10 @@ pg_publication_tables| SELECT p.pubname,
14411441
n.nspname AS schemaname,
14421442
c.relname AS tablename
14431443
FROM pg_publication p,
1444+
LATERAL pg_get_publication_tables((p.pubname)::text) gpt(relid),
14441445
(pg_class c
14451446
JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
1446-
WHERE (c.oid IN ( SELECT pg_get_publication_tables.relid
1447-
FROM pg_get_publication_tables((p.pubname)::text) pg_get_publication_tables(relid)));
1447+
WHERE (c.oid = gpt.relid);
14481448
pg_replication_origin_status| SELECT pg_show_replication_origin_status.local_id,
14491449
pg_show_replication_origin_status.external_id,
14501450
pg_show_replication_origin_status.remote_lsn,

0 commit comments

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