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 and ID_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. 50 ID_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 completed in 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 executes in 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*50 takes 16*2 secs only, but 
executing 1*800 takes about 3000 seconds...

Regards,
Ingolf


-----Original Message-----
From: Sebastian Dressler [mailto:sebast...@swarm64.com] 
Sent: 30 January 2021 11:45
To: Markhof, Ingolf <ingolf.mark...@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.mark...@de.verizon.com> 
> wrote:
> 
> Hi!
>  
> I am struggling with the slow performance when running queries referring to 
> foreign tables. – Yes, I know… - Please read the 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 used for production, the reporting 
> database is for analysis. On the reporting database, the only way to access 
> product data is 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 acceptable time. A query executed on 
> the Oracle reporting server returns data in e.g. 30 seconds. But running the 
> query translated to 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 could explain the different run times? 
> Could there be some tuning option in PostgreSQL to make queries via foreign 
> tables faster (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 
probable root 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-running queries. IMO, the worst-case 
scenario is that a sequential table scan without any filtering, and a single 
worker runs 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-backed child 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 extension can 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 | sebast...@swarm64.com



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

Reply via email to