Обсуждение: pgsql: Fix O(N^2) performance issue in pg_publication_tables view.

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

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/166f69f769c83ef8759d905bf7f1a9aa1d97a340

Modified Files
--------------
src/backend/catalog/system_views.sql | 7 ++++---
src/include/catalog/catversion.h     | 2 +-
src/test/regress/expected/rules.out  | 4 ++--
3 files changed, 7 insertions(+), 6 deletions(-)

=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello@gmail.com> writes:
> Just one doubt, why use LATERAL with pg_get_publication_tables SRF instead
> of JOIN direct to pg_publication_rel?

Yes, LATERAL is just a noise word in this context, but I think
it's good for documentation purposes.

If you're asking why I used a comma instead of JOIN ... ON TRUE,
it's because I don't find the latter to be particularly readable
or good style.  YMMV.

(I agree it's a little weird that the original coding had a mix
of comma and JOIN syntax, but I did not feel a need to revisit
that.)

            regards, tom lane

=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziomello@gmail.com> writes:
> There are some case I missed to force us to use pg_get_publication_tables
> SRF instead of use pg_publication_rel ??

pg_publication_rel isn't going to provide the correct expansion of
a FOR ALL TABLES publication, IIUC.

            regards, tom lane