CODEWITHSUNDEEP
oracle-databasepostgresqlparallel-processingoracle-fdw
JeyJ
JeyJ

Reputation: 4070

Postgresql 9.6 Parallel execution from foreign table

I upgraded my postgresql from 9.5 to 9.6 in order to use the parallel execution to improve my performance. However, I didnt succeed to use it. In my main database almost all my selects look like : select * from foreign_table The foreign table is a foreign table that is located on an oracle database. Some of the tables are big 10G+ and 1,000,000+ records so parallel query should help me alot in this case of select.

The parameters that i configured : 

min_parallel_relation_size = 200MB
max_parallel_workers_per_gather = 2 
max_worker_processes = 8

When I try to use explain analyze select * from a big table that her size is 1.5G and has 5,000,000 records I see only foreign scan : 

 Foreign Scan on customer_prod  (cost=10000.00..20000.00 rows=1000 
width=2438) (actual time=6.337..231408.085 rows=5770616 loops
=1)
  Oracle query: ......
 Planning time: 2.827 ms
 Execution time: 232198.137 ms

*I also tried select * from foreign_table where 1=1 but still same result.

On the other hand the next code worked :

   postgres=# CREATE TABLE people_mariel_test (id int PRIMARY KEY NOT NULL, age int NOT NULL);
   CREATE TABLE
   postgres=# INSERT INTO people_mariel_test  SELECT id, (random()*100)::integer AS age FROM generate_series(1,10000000) AS id;
   INSERT 0 10000000
   postgres=# explain analyze select * from people_mariel_test where age=6;
                                                            QUERY PLAN

   --------------------------------------------------------------------------------
   ------------------------------------------------
   ----------
   Gather  (cost=1000.00..123777.76 rows=50000 width=8) (actual        time=0.239..771.801 rows=99409 loops=1)
      Workers Planned: 1
      Workers Launched: 1
      ->  Parallel Seq Scan on people_mariel_test  (cost=0.00..117777.76 rows=29412 width=8) (actual time=0.045..748.213 rows=49704
    loops=2)
     Filter: (age = 6)
     Rows Removed by Filter: 4950296
    Planning time: 0.261 ms
    Execution time: 785.924 ms
   (8 rows)

Any idea how can I continue?

Upvotes: 1

Views: 586

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

From documentation:

A ForeignScan node can, optionally, support parallel execution. A parallel ForeignScan will be executed in multiple processes and should return each row only once across all cooperating processes. To do this, processes can coordinate through fixed size chunks of dynamic shared memory. This shared memory is not guaranteed to be mapped at the same address in every process, so pointers may not be used. The following callbacks are all optional in general, but required if parallel execution is to be supported.

I have searched source code for Oracle FDW by Laurenz Albe and it does not implement IsForeignScanParallelSafe and thus cannot use parallel execution.

Upvotes: 2

Related Questions