From: Edilmar Alves <edilmaralves@intersite.com.br>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Slow pg_publication_tables with many schemas and tables
Date: Fri, 27 Sep 2019 10:25:24 -0400
Message-ID: <cf6dc1f6-5336-520a-4c3c-cd23846988c7@intersite.com.br> (raw)
In-Reply-To: <14071.1569539499@sss.pgh.pa.us>
References: <5e771308-25dc-15a5-a60d-d86beab71d3d@intersite.com.br>
<14071.1569539499@sss.pgh.pa.us>
Hi,
Em 26/09/2019 19:11, Tom Lane escreveu:
> 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 ...
My design is this because I have a system with 50 enterprises using the
same server.
Before each enterprise used a separated database, and my webapp had a
connection pool
for each database. Then, if for example, my connection pool had
minconn=10 and maxconn=20,
it was totalminconn=500 and totalmaxconn=1000. When I migrated to just
one database and 50
schemas, it was so better to manage just one connection pool, minor
hardware resource usage.
>
>> 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?
The Analyze from original VIEW and the VIEW suggested below
for PGv12 update have a flow diagram very similar, just one
step better in the updated version, for my cenario with 50 schemas.
>
>> 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.
I know my VIEW is not a general purpose solution.
I just submitted this message to the group because
in this kind of situation of many schemas and tables/schema,
the original VIEW and the VIEW below suggested to become
the new on in PGv12 run very slow.
>
> Please see
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> regarding useful ways to present performance problems.
>
> regards, tom lane
--
Attachments:
[image/png] AssEmail.png (68.3K, ../cf6dc1f6-5336-520a-4c3c-cd23846988c7@intersite.com.br/3-AssEmail.png)
download | view image
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: edilmaralves@intersite.com.br, pgsql-performance@lists.postgresql.org
Subject: Re: Slow pg_publication_tables with many schemas and tables
In-Reply-To: <cf6dc1f6-5336-520a-4c3c-cd23846988c7@intersite.com.br>
* 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