Re: pgsql: Fix O(N^2) performance issue in pg_publication_tables view.

On Wed, May 22, 2019 at 12:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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(-)
>

Just one doubt, why use LATERAL with pg_get_publication_tables SRF instead
of JOIN direct to pg_publication_rel?

Regards,

--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento