RE: Foreign table performance issue / PostgreSQK vs. ORACLE

Hi!

My PostgreSQL version is 11.8. 

The query I am running is referring to a number of foreign tables. The first one (table1) has to IDs, let's say ID_A
andID_B. While ID_A is unique, ID_B is not. In my case, I am pulling formation for a value of IB_B for which about 800
rows(with unique ID_A) exist. I found:
 

While

    select * from my_view where id_b='some value';

seemingly runs "forever" (I cancelled execution after a few hours), the following completes in about 1 hr:

    select * from my_view where ia_a in (
        select id_a from table1 where id_b='some value'
     );

So, I tried smaller chunks of ID_a and found the execution time is non-linear with respect to number of IDs. For e.g.
50ID_A's, it was completed in about 12 sec.
 

Next I have split the ~800 ID_A's into chunks of 50 and submitted these 16 queries one after another. They all
completedin about 12 secs, each.
 

I then found the option fetch_size, e.g. ALTER SERVER some_server OPTIONS (fetch_size '50000'). A chunk of 50 now
executesin 2 seconds (instead of 12 before).
 

So, I found the "size" of the query has a serious impact to the execution time. I don't really understand why execution
16*50takes 16*2 secs only, but executing 1*800 takes about 3000 seconds...
 

Regards,
Ingolf


-----Original Message-----
From: Sebastian Dressler [mailto:sebastian@swarm64.com] 
Sent: 30 January 2021 11:45
To: Markhof, Ingolf <ingolf.markhof@de.verizon.com>
Cc: pgsql-general@lists.postgresql.org
Subject: [E] Re: Foreign table performance issue / PostgreSQK vs. ORACLE

Hi Ingolf,

> On 29. Jan 2021, at 13:56, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote:
> 
> Hi!
>  
> I am struggling with the slow performance when running queries referring to foreign tables. – Yes, I know… - Please
readthe whole story!
 

Done and it rings a bell or two.

> The set-up basically is a production database and a reporting database. As names indicate, the production database is
usedfor production, the reporting database is for analysis. On the reporting database, the only way to access product
datais via foreign tables that link to the related production tables.
 
>  
> Now, while some queries on the reporting service run fine, some don't even return any data after hours.
>  
> However, the same set-up worked fine in Oracle before. Reporting wasn't always fast, but it delivered results in
acceptabletime. A query executed on the Oracle reporting server returns data in e.g. 30 seconds. But running the query
translatedto PostgreSQL on the PostgreSQL DB does not deliver a single row after hours (!) of run time.
 
>  
> So, I wonder: Is there a fundamental difference between Oracle database links and foreign tables in PostgreSQL that
couldexplain the different run times? Could there be some tuning option in PostgreSQL to make queries via foreign
tablesfaster (e.g. I heard about option fetch_size)?
 

You did not explicitly mention it, but I assume you are using postgres_fdw to connect from reporting (R) to production
(P).Thomas and Tom already mentioned incomplete/non-existing/non-applicable filter pushdowns. I want to add another
probableroot cause to the list explaining the behavior you experience.
 

The postgres_fdw uses a CURSOR on P to execute the query. While this guarantees transaction safety, it also prohibits
parallelism(PostgreSQL server-side cursors enforce a sequential plan).
 

As a result, depending on the size of tables, indexes, and filters pushed down (or not), this probably results in
slow-runningqueries. IMO, the worst-case scenario is that a sequential table scan without any filtering, and a single
workerruns on the target.
 

Of course, you can try to optimize schemas on P and queries on R, enabling more filter pushdown and eventually a faster
execution.However, I believe this does not work with your entire workload, i.e. there will always be performance gaps.
 

The parallelism issue is theoretically fixable by utilizing partitions on P. R then connects to P with multiple
postgres_fdw-backedchild tables. However, this will only work with a patch to postgres_fdw to implement
"IsForeignScanParallelSafe"(see [1] for a possible implementation). Without this method, there will be no parallelism
again.Without, the partitions scan occurs sequentially, not showing a performance gain.
 

I want to mention there are proprietary options available (re-)enabling PostgreSQL parallelism with cursors. Such an
extensioncan potentially fix your performance issue. However, I have not tried it so far with a setup similar to
yours.

Cheers,
Sebastian


[1]:
https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_swarm64_parallel-2Dpostgres-2Dfdw-2Dpatch&d=DwIGaQ&c=udBTRvFvXC5Dhqg7UHpJlPps3mZ3LRxpb6__0PomBTQ&r=ivZWA-ECVj3XrXBe0obDwKY7Ui7K5Nj9oD2KKWLm0Bw&m=urVtRLfrc1kNan7AL2Al4g0Dq-bCi5UPxtnOEzHlj_U&s=ZkvPe7hWFG3H6Q2q9bca7l984-UxMeNw1fFOAyLWlPg&e=



--

Sebastian Dressler, Solution Architect, Swarm64 AS
+49 30 994 0496 72 | sebastian@swarm64.com



Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer:
DetlefEppig - Vorsitzender des Aufsichtsrats: Francesco de Maio