MIME-Version: 1.0
References: 
 <CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q@mail.gmail.com>
 <CAMkU=1x5m60yaz5sriL12LF=53C8PTZbGiOGtb0Z159nTDKSrg@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1x5m60yaz5sriL12LF=53C8PTZbGiOGtb0Z159nTDKSrg@mail.gmail.com>
From: richard coleman <rcoleman.ascentgl@gmail.com>
Date: Thu, 3 Aug 2023 12:16:14 -0400
Message-ID: 
 <CAGA3vBtr=LjXtugfL5352PiRe0vrUm+jPpZTN=u8NT48BLf3yA@mail.gmail.com>
Subject: Re: analyze foreign tables
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000ec06d0602071a8d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGA3vBtr%3DLjXtugfL5352PiRe0vrUm%2BjPpZTN%3Du8NT48BLf3yA%40mail.gmail.com>
Precedence: bulk

--0000000000000ec06d0602071a8d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Jeff,

In my experience the overhead is directly related to the size and the
complexity of the tables in the query.

A simple query only referencing a small < 5M table with only a primary key,
it isn't noticeable.

A typical complicated query referencing numerous tables ranging in sizes
from <5M to > 2.5T each with a primary key and 0 - 30 indices (per table),
it can add 30 seconds to many minutes to each run of the query.  When some
of these queries are re-run constantly it becomes untenable.  In one case,
manually running analyze on the foreign tables and then running the query
it returns in about 30 sec. Setting use_remote_estimate =3D true made each
run return in about 5 minutes.

Unfortunately, manually running analyze on each foreign table in the schema
(500+ tables, ranging from < 1M to > 3T) takes more than a day to
complete.  On the server hosting the tables, the auto analyze is running
constantly, as expected.  All of the clusters are sitting at PostgreSQL 15.

Hence my desire to find a more performant, less resource intensive way to
pass the continuously updated statistics of these tables to the other
PostgreSQL clusters holding the foreign table pointers to them.

I know it's anecdotal, but I hope it helps anyway.

rik.


On Thu, Aug 3, 2023 at 10:19=E2=80=AFAM Jeff Janes <jeff.janes@gmail.com> w=
rote:

> On Tue, Aug 1, 2023 at 9:47=E2=80=AFAM richard coleman <
> rcoleman.ascentgl@gmail.com> wrote:
>
>>
>> use_remote_estimate isn't really a solution as it adds way too much
>> overhead and processing time to every query run.
>>
>
> Maybe this is the thing which should be addressed.  Can you quantify what
> you see here?  How much overhead is being added for each query?  Is this
> principally processing time, or network latency?
>
>
> Since these tables are being continuously analyzed in the database that
>> hosts the data, is there some way that they statistics could be easily
>> passed through the foreign server mechanism to the remote database that'=
s
>> calling the query?
>>
>
> Since FDW can cross version boundaries, it is hard to see how this would
> work.  Maybe something could be done for the special case of where the
> versions match. I think collations/encoding would be a problem, though.
>
>
>> What I am hoping for is either:
>>
>> 2. add the ability to automatically run analyze on foreign tables just a=
s
>> they are currently run on local tables.
>>
>
> That wouldn't work because communication is always initiated on the wrong
> side.  But it should be fairly easy to script something outside of the
> database which would connect to both, and poll the "foreign"
> pg_stat_all_tables.last_autovacuum and initiate a local ANALYZE for each
> table which was recently autoanalyzed on the foreign side.
>
> Cheers,
>
> Jeff
>

--0000000000000ec06d0602071a8d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Jeff,<div>=C2=A0<div>In my experience the overhead is dire=
ctly related to the size and the complexity of the tables in the query.</di=
v><div><br></div></div><div>A simple query only referencing a small &lt; 5M=
 table with only a primary key, it isn&#39;t noticeable.</div><div><br></di=
v><div>A typical complicated query referencing numerous tables ranging in s=
izes from &lt;5M to &gt; 2.5T each with a primary key and 0 - 30 indices (p=
er table), it can add 30 seconds to many minutes to each run of the query.=
=C2=A0 When some of these queries are re-run constantly it becomes untenabl=
e.=C2=A0 In one case, manually running analyze on the foreign tables and th=
en running the query it returns in about 30 sec. Setting use_remote_estimat=
e =3D true made each run return in about 5 minutes.</div><div><br></div><di=
v>Unfortunately, manually running analyze on each foreign table in the sche=
ma (500+ tables, ranging from &lt; 1M to &gt; 3T) takes more than a day to =
complete.=C2=A0 On the server hosting the tables, the auto analyze is runni=
ng constantly, as expected.=C2=A0 All of the clusters are sitting at Postgr=
eSQL 15.</div><div><br></div><div>Hence my desire to find a more performant=
, less resource intensive way to pass the continuously updated statistics o=
f these tables to the other PostgreSQL clusters holding the foreign table p=
ointers to them.</div><div><br></div><div>I know it&#39;s anecdotal, but I =
hope it helps anyway.</div><div><br></div><div>rik.</div><div><br></div></d=
iv><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On =
Thu, Aug 3, 2023 at 10:19=E2=80=AFAM Jeff Janes &lt;<a href=3D"mailto:jeff.=
janes@gmail.com">jeff.janes@gmail.com</a>&gt; wrote:<br></div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On T=
ue, Aug 1, 2023 at 9:47=E2=80=AFAM richard coleman &lt;<a href=3D"mailto:rc=
oleman.ascentgl@gmail.com" target=3D"_blank">rcoleman.ascentgl@gmail.com</a=
>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex"><div dir=3D"ltr"><div><br></div><div>use_remote_est=
imate isn&#39;t really a solution as it adds way too much overhead and proc=
essing time to every query run.<br></div></div></blockquote><div><br></div>=
<div>Maybe this is the thing which should be addressed.=C2=A0 Can you=C2=A0=
quantify what you see here?=C2=A0 How much overhead is being added for each=
 query?=C2=A0 Is this principally processing time, or network latency?</div=
><div><br></div><div><br></div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex"><div dir=3D"ltr"><div>Since these tables are being continuously analy=
zed in the database that hosts the data, is there some way that they statis=
tics could be easily passed through the foreign server mechanism to the rem=
ote database that&#39;s calling the query?</div></div></blockquote><div><br=
></div><div>Since FDW can cross version boundaries, it is hard to see how t=
his would work.=C2=A0 Maybe something could be done for the special case of=
 where the versions match. I think collations/encoding would be a problem, =
though.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x"><div dir=3D"ltr"><div><br></div><div>What I am hoping for is either:</di=
v><div><br></div></div></blockquote><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div dir=3D"ltr"><div>2. add the ability to automatically run an=
alyze on foreign tables just as they are currently run on local tables.</di=
v></div></blockquote><div><br></div><div>That wouldn&#39;t work because com=
munication is always initiated on the wrong side.=C2=A0 But it should be fa=
irly easy to script something outside of the database which would connect t=
o both, and poll the &quot;foreign&quot; pg_stat_all_tables.last_autovacuum=
 and initiate a local ANALYZE for each table which was recently autoanalyze=
d on the foreign side.=C2=A0</div><div><br></div><div>Cheers,</div><div><br=
></div><div>Jeff</div></div></div>
</blockquote></div>

--0000000000000ec06d0602071a8d--