search

dalibo / pev2

Postgres Explain Visualizer 2
https://explain.dalibo.com
PostgreSQL License
2.45k stars 116 forks source link

PG14 : New node type "Async Foreign Scan" #382

Open blogh opened 3 years ago

blogh commented 3 years ago

PostgreSQL can now execute queries in Foreign server in an asynchronous manner.

Before the executor visited execution nodes one-by-one. Considering sharding, Append on multiple postgres_fdw nodes can work simultaneously and that can largely shorten the respons of the whole query. For example, aggregations that can be pushed-down to remote would be accelerated by the number of remote servers. Even other than such an extreme case, collecting tuples from multiple servers also can be accelerated by tens of percent.

Discussion :

Commit Fest entry :

Set up :

-- local server

CREATE DATABASE localdb;
CREATE ROLE localuser WITH LOGIN PASSWORD 'secret';
\c localdb
CREATE EXTENSION postgres_fdw;
CREATE SERVER foreigndb_fdw1 FOREIGN DATA WRAPPER postgres_fdw 
    OPTIONS (host '127.0.0.1', port '5442', dbname 'foreigndb', async_capable 'true');
CREATE SERVER foreigndb_fdw2 FOREIGN DATA WRAPPER postgres_fdw 
    OPTIONS (host '127.0.0.1', port '5443', dbname 'foreigndb', async_capable 'true');
CREATE SERVER foreigndb_fdw1 FOREIGN DATA WRAPPER postgres_fdw 
    OPTIONS (host '127.0.0.3', port '5444', dbname 'foreigndb', async_capable 'true');

CREATE USER MAPPING FOR localuser SERVER foreigndb_fdw1
    OPTIONS (user 'fdwuser', password 'secret');
CREATE USER MAPPING FOR localuser SERVER foreigndb_fdw2
    OPTIONS (user 'fdwuser', password 'secret');
CREATE USER MAPPING FOR localuser SERVER foreigndb_fdw3
    OPTIONS (user 'fdwuser', password 'secret');

GRANT USAGE ON FOREIGN SERVER foreigndb_fdw TO localuser;

CREATE TABLE test_fdw_part (srv text, ts timestamp, i int)
    PARTITION BY LIST ( srv );
CREATE TABLE test_fdw_part_local 
    PARTITION OF test_fdw_part FOR VALUES IN ('local');
ALTER TABLE test_fdw_part OWNER TO localuser;
ALTER TABLE test_fdw_part_local OWNER TO localuser;

-- foreign server fdw1

-- pg_hba entry : 
-- host    foreigndb       fdwuser         127.0.0.1/32            md5  
-- postgresql.conf :
-- listen_addresses = '*'

CREATE DATABASE foreigndb;
\c foreigndb
CREATE USER fdwUser WITH PASSWORD 'secret';
GRANT USAGE ON SCHEMA PUBLIC TO fdwUser;

CREATE TABLE test_fdw_part_fdw1 (srv text, ts timestamp, i int);
ALTER TABLE test_fdw_part_fdw1 
    ADD CONSTRAINT test_fdw_part_fdw1_srv CHECK (srv = 'fdw1');
ALTER TABLE test_fdw_part_fdw1 OWNER TO fdwuser ;

-- foreign server fdw2

-- pg_hba entry : 
-- host    foreigndb       fdwuser         127.0.0.1/32            md5  
-- postgresql.conf :
-- listen_addresses = '*'

CREATE DATABASE foreigndb;
\c foreigndb
CREATE USER fdwUser WITH PASSWORD 'secret';
GRANT USAGE ON SCHEMA PUBLIC TO fdwUser;

CREATE TABLE test_fdw_part_fdw2 (srv text, ts timestamp, i int);
ALTER TABLE test_fdw_part_fdw2 
    ADD CONSTRAINT test_fdw_part_fdw2_srv CHECK (srv = 'fdw2');
ALTER TABLE test_fdw_part_fdw2 OWNER TO fdwuser ;

-- foreign server fdw3

-- pg_hba entry : 
-- host    foreigndb       fdwuser         127.0.0.1/32            md5  
-- postgresql.conf :
-- listen_addresses = '*'

CREATE DATABASE foreigndb;
\c foreigndb
CREATE USER fdwUser WITH PASSWORD 'secret';
GRANT USAGE ON SCHEMA PUBLIC TO fdwUser;

CREATE TABLE test_fdw_part_fdw3 (srv text, ts timestamp, i int);
ALTER TABLE test_fdw_part_fdw3 
    ADD CONSTRAINT test_fdw_part_fdw3_srv CHECK (srv = 'fdw3');
ALTER TABLE test_fdw_part_fdw3 OWNER TO fdwuser ;

-- local server

\c localdb
CREATE FOREIGN TABLE test_fdw_part_fdw1 
    PARTITION OF test_fdw_part FOR VALUES IN ('fdw1') 
    SERVER foreigndb_fdw1 ;
CREATE FOREIGN TABLE test_fdw_part_fdw2 
    PARTITION OF test_fdw_part FOR VALUES IN ('fdw2') 
    SERVER foreigndb_fdw2 ;
CREATE FOREIGN TABLE test_fdw_part_fdw3 
    PARTITION OF test_fdw_part FOR VALUES IN ('fdw3') 
    SERVER foreigndb_fdw3 ;

ALTER FOREIGN TABLE test_fdw_part_fdw1 OWNER TO localuser ;
ALTER FOREIGN TABLE test_fdw_part_fdw2 OWNER TO localuser ;
ALTER FOREIGN TABLE test_fdw_part_fdw3 OWNER TO localuser ;

INSERT INTO test_fdw_part (srv, ts, i)
    SELECT 'fdw1', current_timestamp + INTERVAL '1 minute' * x, x 
    FROM generate_series(1, 400000) AS F(x);

INSERT INTO test_fdw_part (srv, ts, i)
    SELECT 'fdw2', current_timestamp + INTERVAL '1 minute' * x, x 
    FROM generate_series(1, 400000) AS F(x);

INSERT INTO test_fdw_part (srv, ts, i)
    SELECT 'fdw3', current_timestamp + INTERVAL '1 minute' * x, x 
    FROM generate_series(1, 400000) AS F(x);

INSERT INTO test_fdw_part (srv, ts, i)
    SELECT 'local', current_timestamp + INTERVAL '1 minute' * x, x 
    FROM generate_series(1, 400000) AS F(x);

Example plan : 

[local]:5438 localuser@localdb=> EXPLAIN (ANALYZE, BUFFERS, VERBOSE ) SELECT srv, count(*) FROM test_fdw_part GROUP BY 1;
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=11053.44..11053.48 rows=4 width=14) (actual time=717.535..717.537 rows=4 loops=1)
   Output: test_fdw_part.srv, count(*)
   Group Key: test_fdw_part.srv
   Batches: 1  Memory Usage: 24kB
   Buffers: shared hit=2548
   ->  Append  (cost=100.00..9031.51 rows=404386 width=6) (actual time=1.808..517.940 rows=1600000 loops=1)
         Buffers: shared hit=2548
         ->  Async Foreign Scan on public.test_fdw_part_fdw1 test_fdw_part_1  (cost=100.00..153.86 rows=1462 width=32) (actual time=0.001..22.216 rows=400000 loops=1)
               Output: test_fdw_part_1.srv
               Remote SQL: SELECT srv FROM public.test_fdw_part_fdw1
         ->  Async Foreign Scan on public.test_fdw_part_fdw2 test_fdw_part_2  (cost=100.00..153.86 rows=1462 width=32) (actual time=0.000..21.770 rows=400000 loops=1)
               Output: test_fdw_part_2.srv
               Remote SQL: SELECT srv FROM public.test_fdw_part_fdw2
         ->  Async Foreign Scan on public.test_fdw_part_fdw3 test_fdw_part_3  (cost=100.00..153.86 rows=1462 width=32) (actual time=0.000..21.668 rows=400000 loops=1)
               Output: test_fdw_part_3.srv
               Remote SQL: SELECT srv FROM public.test_fdw_part_fdw3
         ->  Seq Scan on public.test_fdw_part_local test_fdw_part_4  (cost=0.00..6548.00 rows=400000 width=6) (actual time=0.022..36.955 rows=400000 loops=1)
               Output: test_fdw_part_4.srv
               Buffers: shared hit=2548
 Planning Time: 0.245 ms
 Execution Time: 718.941 ms
(21 rows)

PEV2 produces the following output for this plan : https://explain.dalibo.com/plan/KVB

I think the calculation of the duration of the append node is false since the "Async Foreign Scans" are done at the same time. Right now, I am not sure how it should be computed ...

Krysztophe commented 3 years ago

just in case, 2 examples from our workshop of the same query with and without async:

https://explain.dalibo.com/plan/QEC https://explain.dalibo.com/plan/j6V

(merci @fljdin )

pgiraud commented 2 years ago

The parse of the "Async Foreign Scan" is now supported. I'm leaving this ticket open since there may be improvements to be done on the calculation.