From: Tom Lane <tgl@sss.pgh.pa.us>
To: Edilmar Alves <edilmaralves@intersite.com.br>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow pg_publication_tables with many schemas and tables
Date: Thu, 26 Sep 2019 19:11:39 -0400
Message-ID: <14071.1569539499@sss.pgh.pa.us> (raw)
In-Reply-To: <5e771308-25dc-15a5-a60d-d86beab71d3d@intersite.com.br>
References: <5e771308-25dc-15a5-a60d-d86beab71d3d@intersite.com.br>
Edilmar Alves <edilmaralves@intersite.com.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, edilmaralves@intersite.com.br, pgsql-performance@lists.postgresql.org
Subject: Re: Slow pg_publication_tables with many schemas and tables
In-Reply-To: <14071.1569539499@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox