From: | Edilmar Alves <edilmaralves(at)intersite(dot)com(dot)br> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Slow pg_publication_tables with many schemas and tables |
Date: | 2019-09-26 20:37:02 |
Message-ID: | 5e771308-25dc-15a5-a60d-d86beab71d3d@intersite.com.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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:
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-09-26 23:11:39 | Re: Slow pg_publication_tables with many schemas and tables |
Previous Message | Pavel Stehule | 2019-09-26 08:31:10 | Re: Analyze on slave promoted. |