]> granicus.if.org Git - postgresql/commit
Fix O(N^2) performance issue in pg_publication_tables view.
authorTom Lane <tgl@sss.pgh.pa.us>
Wed, 22 May 2019 15:46:57 +0000 (11:46 -0400)
committerTom Lane <tgl@sss.pgh.pa.us>
Wed, 22 May 2019 15:47:02 +0000 (11:47 -0400)
commit166f69f769c83ef8759d905bf7f1a9aa1d97a340
treee4e7dcc6de3e1bac0007126c42451fb1e0cef169
parent728840fe13acff7b03a4b1a813eeb8900ce5e469
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 even
if it were, there's no way for it to know that the output of
pg_get_publication_tables() is duplicate-free, making the de-duplicating
semantics of IN unnecessary.  Hence, rewrite as a LATERAL sub-query.
This provides circa 100X speedup for me with a few hundred published
tables (the whole regression database), and things would degrade as
roughly 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 know
we'll be doing a catversion bump before beta2 to fix pg_statistic_ext
issues, 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
src/backend/catalog/system_views.sql
src/include/catalog/catversion.h
src/test/regress/expected/rules.out