pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17870: Analyze on remote postgresql_fdw table never finish
3+ messages / 3 participants
[nested] [flat]
* BUG #17870: Analyze on remote postgresql_fdw table never finish
@ 2023-03-27 07:35 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2023-03-27 07:35 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: maxim.boguk@gmail.com
The following bug has been logged on the website:
Bug reference: 17870
Logged by: Maxim Boguk
Email address: maxim.boguk@gmail.com
PostgreSQL version: 13.9
Operating system: Linux
Description:
Hi,
I found strange situation when I try to populate local statistic from remote
postgesql_fdw table:
analyze verbose cold_replica_fdw.interview_review_info_archive;
- never ends,
Local and remote DB in the same datacenter with more total bandwidth 20Gbps
and latency under 0.1ms (both tested/verified), so no network issues.
What I see on local side with set client_min_messages to debug5;:
hh_data=# analyze verbose cold_replica_fdw.interview_review_info_archive;
DEBUG: discarding connection 0x563656981900
DEBUG: sinval catchup complete, cleaning queue
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state:
INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: new postgres_fdw connection 0x56365698fc40 for server "cold_replica"
(user mapping oid 263996607, userid 10)
DEBUG: starting remote transaction on connection 0x56365698fc40
INFO: analyzing "cold_replica_fdw.interview_review_info_archive"
and stays there for hours...
What I see on the remove database with log_min_duration_statement set to 0
to FDW user:
2023-03-27 10:11:24.763 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/0 txid:0] [SET] LOG: duration: 0.086 ms statement: SET
application_name='postgres_fdw';
2023-03-27 10:11:24.763 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/0 txid:0] [SET] LOG: duration: 0.029 ms statement: SET
search_path = pg_catalog
2023-03-27 10:11:24.764 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/0 txid:0] [SET] LOG: duration: 0.166 ms statement: SET timezone =
'UTC'
2023-03-27 10:11:24.764 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/0 txid:0] [SET] LOG: duration: 0.023 ms statement: SET datestyle
= ISO
2023-03-27 10:11:24.765 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/0 txid:0] [SET] LOG: duration: 0.032 ms statement: SET
intervalstyle = postgres
2023-03-27 10:11:24.765 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/0 txid:0] [SET] LOG: duration: 0.032 ms statement: SET
extra_float_digits = 3
2023-03-27 10:11:24.766 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/356802672 txid:0] [START TRANSACTION] LOG: duration: 0.021 ms
statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
2023-03-27 10:11:24.767 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/356802672 txid:0] [SELECT] LOG: duration: 0.446 ms statement:
SELECT
pg_catalog.pg_relation_size('public.interview_review_info_archive'::pg_catalog.regclass)
/ 8192
2023-03-27 10:11:24.861 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/356802672 txid:0] [DECLARE CURSOR] LOG: duration: 92.942 ms
statement: DECLARE c1 CURSOR FOR SELECT topic_id, review_id,
move_to_invitation_state_time, no_interview_reply_time,
review_suggestion_chat_message_creation_time FROM
public.interview_review_info_archive
2023-03-27 10:11:28.268 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/356802672 txid:0] [FETCH] LOG: duration: 3406.462 ms statement:
FETCH 100 FROM c1
2023-03-27 10:11:28.269 UTC 636130
negotiation_chat_archive_ro.fdw@negotiation_chat_archive from [local]
[vxid:39/356802672 txid:0] [FETCH] LOG: duration: 0.094 ms statement:
FETCH 100 FROM c1
and then continue fetch forever, on the last attempt it fetched from cursor
over 5M times before I canceled analyze statement.
public.interview_review_info_archive - huge native partitioned by range
table with 100 partitions and 2TB+ size.
Is it expected that the analyze of foreign table will fetch whole table to
local size (quite a task for 2TB table)?
PS: I don't want enable a remote estimates because it adds huge planning
overhead (with remote_estimate 'true'):
Foreign Scan on cold_replica_fdw.interview_review_info_archive
(cost=14.10..14.14 rows=1 width=40) (actual time=3.966..4.681 rows=1
loops=1)
Output: topic_id, review_id, move_to_invitation_state_time,
no_interview_reply_time, review_suggestion_chat_message_creation_time
Remote SQL: SELECT topic_id, review_id, move_to_invitation_state_time,
no_interview_reply_time, review_suggestion_chat_message_creation_time FROM
public.interview_review_info_archive ORDER BY topic_id ASC NULLS LAST LIMIT
1::bigint
Planning Time: 13.646 ms
Execution Time: 5.347 ms
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17870: Analyze on remote postgresql_fdw table never finish
@ 2023-03-27 14:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2023-03-27 14:43 UTC (permalink / raw)
To: maxim.boguk@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> Is it expected that the analyze of foreign table will fetch whole table to
> local size (quite a task for 2TB table)?
Yes. There's an improvement in the works for v16 (8ad51b5f4).
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17870: Analyze on remote postgresql_fdw table never finish
@ 2023-03-28 06:34 Maxim Boguk <maxim.boguk@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Maxim Boguk @ 2023-03-28 06:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
On Tue, Mar 28, 2023 at 1:43 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > Is it expected that the analyze of foreign table will fetch whole table
> to
> > local size (quite a task for 2TB table)?
>
> Yes. There's an improvement in the works for v16 (8ad51b5f4).
>
> regards, tom lane
>
Thank you for an explanation.
So using use_remote_estimate only way to work with huge fdw tables atm.
Is this behaviour of analyze over fdw documented somewhere?
--
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-03-28 06:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-27 07:35 BUG #17870: Analyze on remote postgresql_fdw table never finish PG Bug reporting form <noreply@postgresql.org>
2023-03-27 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 06:34 ` Maxim Boguk <maxim.boguk@gmail.com>
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