From: | Mats Julian Olsen <mats(at)duneanalytics(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org, wilhelm(at)duneanalytics(dot)com |
Subject: | Re: postgres_fdw insert extremely slow |
Date: | 2020-11-25 19:01:17 |
Message-ID: | 56a2c3cf-374d-893b-2feb-964805ff0884@duneanalytics.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 25.11.2020 18:12, Tom Lane wrote:
> Mats Julian Olsen <mats(at)duneanalytics(dot)com> writes:
>> On 25.11.2020 17:58, Tom Lane wrote:
>>> ... Have you tried looking into pg_locks on the
>>> remote server while this query is running?
>> Thanks Tom, I'll try to spin up a regular Postgres instance on both rds
>> and ec2 and see if that helps. As for the locks, I can not see any
>> blocked activity on the remote server while the query runs.
> Another place to check is pg_stat_activity, specifically wait_event_type
> and wait_event.
Thanks Tom, I've now setup the fdw from a local postgres instance and
seeing similar timings for the selects and inserts.
I've got some more numbers here:
x (aurora aws eu-west-1) => y (gcp gce us-central-1): ~15 s
local (eu) => y: ~15 s
local (eu) => test 1 (postgres aws rds eu-west-1): ~4 s
local (eu) => test 2 (postgres google cloud sql, us-central-1): ~15s
local (eu) => local (eu): < 1s
SELECTs in all instances are sub-second.
To me this does indicate some sort of networking issue, but I'm
wondering if INSERTs are treated differently than SELECTs in
postgres_fdw? The only feasibly explanation I have is that postgres_fdw
does many more network calls for INSERT than for SELECT, e.g. something
like 1 for SELECT and `n` for INSERT?
Best,
Mats
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-11-25 19:18:57 | Re: postgres_fdw insert extremely slow |
Previous Message | Adrian Klaver | 2020-11-25 17:43:36 | Re: INSERT Trigger to check for existing records : Does this do what I hope it is doing? |