From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | PegoraroF10 <marcos(at)f10(dot)com(dot)br> |
Cc: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Re: Refresh Publication takes hours and doesn´t finish |
Date: | 2019-05-20 21:30:04 |
Message-ID: | 12632.1558387804@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
PegoraroF10 <marcos(at)f10(dot)com(dot)br> writes:
> I tried sometime ago ... but with no responses, I ask you again.
> pg_publication_tables is a view that is used to refresh publication, but as
> we have 15.000 tables, it takes hours and doesn´t complete. If I change that
> view I can have an immediate result. The question is: Can I change that view
> ? There is some trouble changing those system views ?
> Original View is ...
> create view pg_catalog.pg_publication_tables as
> SELECT p.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)))
> WHERE (c.oid IN (SELECT pg_get_publication_tables.relid FROM
> pg_get_publication_tables((p.pubname)::text)
> pg_get_publication_tables(relid)));
> This way it takes 45 minutes to respond.
> I changed it to ...
> create or replace pg_catalog.view pg_publication_tables as SELECT p.pubname,
> n.nspname AS schemaname, c.relname AS tablename from pg_publication p inner
> join pg_get_publication_tables(p.pubname) pt on true inner join pg_class c
> on pt.relid = c.oid inner join pg_namespace n ON (n.oid = c.relnamespace);
> This one takes just one or two seconds.
Hmm ... given that pg_get_publication_tables() shouldn't return any
duplicate OIDs, it does seem unnecessarily inefficient to put it in
an IN-subselect condition. Peter, is there a reason why this isn't
a straight lateral join? I get a much saner-looking plan from
FROM pg_publication P, pg_class C
- JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.oid IN (SELECT relid FROM pg_get_publication_tables(P.pubname));
+ JOIN pg_namespace N ON (N.oid = C.relnamespace),
+ LATERAL pg_get_publication_tables(P.pubname)
+ WHERE C.oid = pg_get_publication_tables.relid;
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2019-05-20 21:36:58 | Re: Loading table with indexed jsonb field is stalling |
Previous Message | Fabrízio de Royes Mello | 2019-05-20 21:25:49 | Re: Refresh Publication takes hours and doesn´t finish |
From | Date | Subject | |
---|---|---|---|
Next Message | Fabrízio de Royes Mello | 2019-05-20 21:37:16 | Re: Re: Refresh Publication takes hours and doesn´t finish |
Previous Message | Fabrízio de Royes Mello | 2019-05-20 21:25:49 | Re: Refresh Publication takes hours and doesn´t finish |