To: pgsql-performance@lists.postgresql.org
From: Edilmar Alves <edilmaralves@intersite.com.br>
Subject: Slow pg_publication_tables with many schemas and tables
Message-ID: <5e771308-25dc-15a5-a60d-d86beab71d3d@intersite.com.br>
Date: Thu, 26 Sep 2019 16:37:02 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk

Hi,

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.

I found the discussion in pgsql-general thread:

https://www.postgresql.org/message-id/flat/11566.1558463253%40sss.pgh.pa.us#ec144ebcd8a829010fc82a7fe2abfd3f

but thread was closed.

Then, I sent here in performance list my problem.

-------------------------------------

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');

-------------------------------------

Then, in my case I created a publication for each schema and all tables 
with the same same of the schema, creating 50 publications.

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:

SELECT DISTINCT t.schemaname, t.tablename FROM 
pg_catalog.pg_publication_tables t WHERE t.pubname IN ('mypubschema');

My problem was solved but I think next version of pg should verify this 
problem to find a general solution.