Need parallel append on foreign tables in PostgreSQL

Question

We developed a fdw based on postgres_fdw that implement searchs at big repositories (big data) that keep the data compressed. We are tring to use the concept of postgres partition tables, so that we could parallel the search on many partitions at same time. We need "Parallel Append" for Foreign Data Wrappers.

Any one know if this it will be adressed at Postgres 11?

If my query results in searches at local partitions, postgres uses parallelism, but if it results in foreign scans it does not.

Local partitons:

explain select * from precio where fecha >= '2017-01-20' and fecha <= '2017-01-21' and plusalesprice < 1

Gather (cost=1000.00..969527.35 rows=81568 width=60)
 Workers Planned: 2
 -> Parallel Append  (cost=0.00..960370.55 rows=33986 width=60)
    -> Parallel Seq Scan on precio_20170121  (cost=0.00..589086.00 rows=19293 width=60)
       Filter: ((fecha >= '2017-01-20'::date) AND (fecha <= '2017-01-21'::date) AND (plusalesprice < '1'::numeric))
    -> Parallel Seq Scan on precio_20170120 (cost=0.00..371114.62 rows=14693 width=60)
       Filter: ((fecha >= '2017-01-20'::date) AND (fecha <= '2017-01-21'::date) AND (plusalesprice < '1'::numeric))

Foreign partitions:

explain select * from precio where fecha >= '2017-01-01' and fecha <= '2017-01-02' and plusalesprice < 1

Append (cost=200.00..2650400.00 rows=20000000 width=60)
 -> Foreign Scan on precio_xdr20170101  (cost=200.00..1275200.00 rows=10000000 width=60)
    Filter: ((fecha >= '2017-01-01'::date) AND (fecha <= '2017-01-02'::date) AND (plusalesprice < '1'::numeric))
 -> Foreign Scan on precio_xdr20170102  (cost=200.00..1275200.00 rows=10000000 width=60)
    Filter: ((fecha >= '2017-01-01'::date) AND (fecha <= '2017-01-02'::date) AND (plusalesprice < '1'::numeric))

Parallel append on foreign tables in PostgreSQL 11

score 0 · Answer 1 · answered Dec 06 '18 at 05:30

0

To be able to use Parallel Append, all of children need to be safe to run in parallel workers. postgres_fdw cannot guarantee that safety yet (even as of PostgreSQL 11), so any child tables that are managed by postgres_fdw cannot be scanned in parallel.

answered Dec 06 '18 at 05:30

amitlan

240
2
10

I was able to perform the parallel append to my FDW. Among other things I had to serialize my state structures that need to be shared among the workers. Soon I'll be posting an answer here, explaining better how I did it.In my case I can ensure that all scans in my remote tables can be done in parallel. – Sanyo Moura Dec 07 '18 at 12:05

Need parallel append on foreign tables in PostgreSQL

1 Answers1