Slow pg_publication_tables with many schemas and tables
От | Edilmar Alves |
---|---|
Тема | Slow pg_publication_tables with many schemas and tables |
Дата | |
Msg-id | 5e771308-25dc-15a5-a60d-d86beab71d3d@intersite.com.br обсуждение исходный текст |
Ответы |
Re: Slow pg_publication_tables with many schemas and tables
|
Список | 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: 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.
В списке pgsql-performance по дате отправления: