Re: Slow pg_publication_tables with many schemas and tables

Edilmar Alves <edilmaralves(at)intersite(dot)com(dot)br> writes:
> I use PG 11.5 into CentOS6 server, with 50 schemas, exactly equals in
> tables structure, and more than 400 tables/schema. Then, there is more
> than 20000 tables.

Possibly you should rethink that design, but ...

> I changed the original PG view like said in the above thread:
> CREATE OR REPLACE VIEW pg_catalog.pg_publication_tables AS
> SELECT
> P.pubname AS pubname,
> N.nspname AS schemaname,
> C.relname AS tablename
> FROM pg_publication P, pg_class C
> JOIN pg_namespace N ON (N.oid = C.relnamespace),
> LATERAL pg_get_publication_tables(P.pubname)
> WHERE C.oid = pg_get_publication_tables.relid;
> but the problem continues. It is very slow to process the query used by
> replication system:
> SELECT DISTINCT t.schemaname, t.tablename FROM
> pg_catalog.pg_publication_tables t WHERE t.pubname IN ('mypubschema');

What do you get from EXPLAIN ANALYZE for that?

> After this, I changed the view above to this:
> CREATE OR REPLACE VIEW pg_catalog.pg_publication_tables AS
> SELECT p.pubname, c.schemaname, c.tablename
> FROM pg_publication p
> JOIN pg_tables c ON p.pubname = c.schemaname;
> And the query below became very fast:

As a wise man once said, I can make my program arbitrarily fast
if it doesn't have to give the right answer ... and this query
obviously doesn't produce the correct answer, except in the
contrived special case where the content of a publication is
exactly the content of a schema. So I don't see what your
point is here.

Please see

https://wiki.postgresql.org/wiki/Slow_Query_Questions

regarding useful ways to present performance problems.

regards, tom lane