αααΈααΈααΌααααΎαααΆαααααΌαα
αααΎαα α’ααααααααααΆα
αααΎαααααΆα αααααα·ααΈααΆαααΉααααα»αααααΎαααα½ααα
ααΆααααΌαααααΆααα·ααααααααααααααΆα ααααα·αααΎααΆααΆαααα½ααααΆαααΆααααα
ααΎαα½αααΆα
αααΎααααα»αααΆααΆααα½α ααΆααααΎαααΆαααΏαααΆααα
αααααααΎαααΈααΈααΌα
αααΎα α αΎα PostgreSQL α’αΆα
ααααΎααΆααΆαααΆααααΈαααα 9.6α
ααΆααααΌαα αααΆαααα 3 ααααΆαααΎααααΈα’αα»αααααα»αααΆααααα½αααααααααΆ - ααΎαααααΌααααααααΌαα‘αΎααα·ααα ααααΆααααΆαααααααααααΆαααααα·ααααα·αααα½αα PostgreSQL 9.6 ααΆαααααΆαα αααααΆαα ααΆααααααααααΎααααΈαααααα’ααΌααααααααααα αα αααα»αααααααΆαααααααααΆαα αααααααααα½αααααααααααααΌαααΆαααααα·ααααα·ααααααααΆα
ααΆαααΉααααα·α
- αα»αααΎαααααΎαααΆααααΆαααΆα‘αα ααααα·αααΎααααΌαααΆααα’ααααΆαααααα ααΎαα·αααΌα ααααααααααΎααααααααααΉαααα α»αα
- ααααΆαααααα»α ααααΎαααΆααααΆαααΆα‘ααααΆαα½αααΉαααααα WORK_MEM αααααααααΎα’αααα αα αΆαα αααΎα - ααΆαα hash join α¬ sort ααααΎα’αααα αα αΆα work_mem α
- αααα½α OLTP ααΆαααΊααααΆαααΆααα·αα’αΆα αααααΎαααααΏααααααΆαααααα·ααααα·ααααααααΆααΆαααα α αΎαααααα·αααΎαααα½ααααα‘αααα½ααα½α ααααΎαααΆααααΆαααΆα‘ααααΉαααααΎα±ααααΆααα α»αααα»αααααα
- α’αααα’αα·ααααααα αΌαα α·αααααααΎαααααααΆα TPC-H α αααα ααααΆα’αααααΆααααα½αααααααααααΆαααααΆααααΆαααααα·ααααα·αααΆαααΆα‘ααααααα’α₯αααα ααα
- ααΆααααααα½α SELECT αααααααΆαααΆαα αΆααααααΆαα»αααα»ααααααααααααΌαααΆαααααα·ααααα·ααααααααΆα
- αααααααααΆαααααΎαα·αα·ααααααααΉαααααΌαααΊαααααΎαααΆαααΆααααααααΆααΆααααααααααΆααααααΆαααα»ααααααααΆαααΆα‘ααα
- ααΆαααα’αΆααααα½α αα·αααααααααααα·α αα·αααααΌαααΆαααΆααααααα
- αα»αααΆαβαααα’α½α αα·αβα’αα»ααααβααα»αβαααα»αβαααβααΆαβαααααΆβαα·αβααααβααααΆβααα
- α’ααααα·αααα½αααΆαα’αααΈαα αααα»αααααα»αααΆαααΆα I/O ααα
- αα·αααΆααααα½ααααααααΆαααΆαααααααααααααααΆααα ααα»αααααααα½ααααααΆαααααααα’αΆα ααααΌαααΆαααααα·ααααα·ααααααααΆαααα»ααα·αααααΆααα½αα ααα½αα
- αααα½α CTE (ααΆαα½α ... ) ααΆαα½α SELECT αααααααΌαααΆαααααΆααααΎααααΈααΎαααααΎαααΆααααΆαααΆα‘ααα
- α§ααααααα»ααα·ααααααααΆααΈααΈααΈαα·αααΆααααΆααααααααΎαααΆααααΆαααΆα‘αααα α‘αΎααα (ααα»αααααα½αααα’αΆα !)
- ααΆαβα αΌααα½αβαααβαααβαα·αβααααΌαβααΆαβααΆααααβααα
- max_rows αα·αααααΎαααΆααααΆαααΆα‘ααα
- ααααα·αααΎαααα½ααα½αααΆααα»αααΆαααααα·αααααΌαααΆααααααΆααααΆ PARALLEL SAFE αααααΆααΉαααααΌαααΆαααΆααααΆαααααααα½αα
- ααααα·αα―ααααααα·ααααα·ααΆα SERIALIZABLE αα·αααααΎαααΆααααΆαααΆα‘ααα
ααα·ααααΆαααΆααααα
α’αααα’αα·αααααα PostgreSQL ααΆαααααΆααΆαααΆααααααααααααααΆααααΎααααααααα½αααα TPC-H α ααΆαααααααα·α
- ααΆααα TPC-H_Tools_v2.17.3.zip (α¬ααααααααΈααΆαααα)
ααΈ TPC αααα αααααΆα . - ααααΌαααααα makefile.suite αα
Makefile α αΎαααααΌαααΌα
αααααΆααα·αααααΆαα
ααΈαααα
https://github.com/tvondra/pg_tpch . α αααααααΌααααααααΎααΆααααααααΆ make α - αααααΎααα·ααααααα
./dbgen -s 10
αααααΎαααΌαααααΆααα·αααααα 23 GB α αααααΊαααααααααΆααααΎααααΈααΎαααΆααα»αααααΆαα αααα»αααΆαα’αα»αααααααααα½ααααΆαααΆα‘αααα·ααα·ααααΆαααΆα‘ααα - ααααααα―αααΆα
tbl
Π²csv Ρ for
ΠΈsed
. - ααααΌαααααΆαα
pg_tpch
αα·αα ααααα―αααΆαcsv
Π²pg_tpch/dss/data
. - αααααΎααααα½ααααααααΎααΆααααααααΆ
qgen
. - αααα»ααα·αααααααα
αααα»αααΌαααααΆααα·αααααααααααααΎααΆααααααααΆ
./tpch.sh
.
ααΆααααααααΆαααααΆααααααα
ααΆα’αΆα ααΏαααΆααα·αααααααααΆαααααΆαα’αΆαααααααααΆααααα ααα»αααααααααΆααααα·ααααααααααΌαααΆαααΈαααΆαααΆααα ααΌααΆαα CPU cores ααΆα αααΎαα αα αααα»αααααααααααααα·ααααα·ααΆαααααΎα α―αααΆααα·αααααα PostgreSQL ααααΌαααΆααααααΆαα»ααααΆαααα’α ααΆαα½αααΉαααΆαα’αΆαααΆαα»α ααΆα’αΆα αα αα½α ααΎααααΈααα½αααΆααααα»αααααΆαααΈααΆααααα»αααΆαααααΎ PG daemon α ααΌα αααα ααααΎαααΆααααα½ααα·αααααααααααΈα I/O ααα ααΆααααΎααααΆαααααα CPU αα α
- α’αΆααα½αααααααααα½ααααΈαααααααΆααΆα;
- αααααααααααααααααα’αααα αα·ααααααααα
WHERE
.
αααααααΎαααΆααααα½αααΆαααα select
:
tpch=# explain analyze select l_quantity as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on lineitem (cost=0.00..1964772.00 rows=58856235 width=5) (actual time=0.014..16951.669 rows=58839715 loops=1)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 1146337
Planning Time: 0.203 ms
Execution Time: 19035.100 ms
ααΆααααααααΆαααααΆααααααααααααΎααα½αα αααΎαααααααααααΆαααΆααααααΌααααα»α ααΌα αααααααα½αααααΌαααΆαααααα·ααααα·αααααααΌααααΈααΈααΌαααα½αα
ααααα·αααΎα’ααααααααα SUM()
α’αααα’αΆα
ααΎαααΎαααΆααα αΌαααΆαααΆαααΈαααΉααα½ααααααΎαααααΏααααα½αα
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=1589702.14..1589702.15 rows=1 width=32) (actual time=8553.365..8553.365 rows=1 loops=1)
-> Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 ms
ααΆααααααΌααααα»ααααΆαααΆα‘αα
ααααΆαα Parallel Seq Scan αααααΎααα½αααααααααΆααααΆααααααΌααααα»αααααααααα ααααΆαα "ααΆααααααΌααααα»ααααααααα" ααΆαααααααΆααααΆααααααααααααΎ SUM()
. αα
α
α»ααααα
αα ααααα SUM ααΈααααΎαααΆααααααααα·αα½ααααααΌαααΆααααααΌααααααααΆαα "Gather" α
ααααααα α»ααααααααααΌαααΆαααααΆαααααααΆαα "αααα ααααα»α" α ααααα·αααΎα’αααααΆααα»αααΆααααααΌααααα»αααααΆαααααα½αααααα’ααα αα»αααααα αααααΆαααα½αααΆααΆ "αα»ααααα·ααΆαααααααααΆ"α
α ααα½αααααΎαααΆααααααααααα
α ααα½αααααΎαααΆαααααααααααα’αΆα ααΎαα‘αΎαααααα·αα αΆαααΆα αα αΆααααααΎααααΆαααΈαααα‘αΎααα·αα
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=1589702.14..1589702.15 rows=1 width=32) (actual time=8553.365..8553.365 rows=1 loops=1)
-> Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 ms
ααΎααΆαα’αααΈααΎαα‘αΎααα ααΈααα? ααΆαααααΎαααΆαααΆαααΆαα αααΎαααΆα 2 αα α αΎαααΆαααααΎαα»αααΆααααααΏαααΆα 1,6599 ααα ααΆαααααΆαα½αα±ααα αΆααα’αΆααααααα ααΎαααΆαααααΎαααΆααααααα 2 ααΆαα αα·αα’αααααΉαααΆα 1 ααΆααα αααααΆααααΈααΆαααααΆααααααΌαααΆααΆαααααΆαααΆ 4+1 α
ααααΏαα’αα·ααααΆααααααΎαααΈααααΎαααΆααααΆαααΆα‘ααα 5/3 = 1,66(6) ααα
ααΎααΆααααΎαααΆααααΆαααΌα ααααα ?
ααααΎαααΆα
ααααΎααααα·ααααα·αααααα
αΆααααααΎαααΆαα½αααΉαααααΎαααΆαααΆααα»αα α’αααααΉαααΆαααααΎα’αααΈααααααααΆαααααα·αααααααααΆ αα·αααααΎαααΆααααΆαααΆα‘ααααααα ααααΎαααΆααααααααααααα’αα»ααααααααΎααΌα
ααααΆααααΌαααΆαααα α
ααΆααααΎαααΆαααααααα ααααΎαααΆααααΆαααΆα‘ααααααΎα αααααΆαα
ααΆαααααααα
α’αααααααα
ααααΎαααΆαααααααααααααααΆααααααααΆαα½αα’αααααΉαααΆαααΆαααααα½αααΆα (ααα’ααααΎααΆαα αα αΆααααααΆαα ααααααα)α ααααΎαααΆαααΈαα½ααααΆα 2 αα½α: αααααΆααααα α»α αα·ααααααΆαα tuples α
ααΎααααΌαααΆαααα αΌαααΆαααΆαααα»ααααΆα?
ααααααααα’αααααααΆααααΌαααΆααααααΆααααααααΆαααΆαααααα max_parallel_workers_per_gather
max_parallel_workers size
max_worker_processes
ααααα·αααΎαα·αα’αΆα αααα ααααααΎαααΆαααααααααΆααα ααααΎαααΆαααΉαααΆααααΎαααΆααααα½αα
αααααα·ααΈαααα
ααααααΆααααα½αα’αΆα
ααΆαααααααααα αΌαααΆαααΆαα’αΆαααααααΎααα αααααΆααΆα α¬αααααααααα ααΆααααΆαααΆαααααααααααΆααααΏαααα min_parallel_table_scan_size
min_parallel_index_scan_size
set min_parallel_table_scan_size='8MB'
8MB table => 1 worker
24MB table => 2 workers
72MB table => 3 workers
x => log(x / min_parallel_table_scan_size) / log(3) + 1 worker
ααΆααααααααααΆααΆαααααΆα 3 αα min_parallel_(index|table)_scan_size
, Postgres ααααααααααΎαααΆαααααααα α
ααα½αααα αΌαααΆαααΆααα·αααα’ααααΎααΆαα
αααΆαααα ααΆαα’αΆαααααααΆαααΆααααααααααΎα±ααααΆαα’αα»αααααααα»αααααΆααα·ααΆαα αααα»ααα
αα·α α’ααααααα
ααααααΆαααααΎα
αααΆααααΆααααα
αα
αααα»αααΆαα’αα»αααα α
αααΆααααΆααααααα·αααααααααααααααααΆααααΆαααα·ααα ααΌα
ααααα’αααα’αΆα
ααααΆααααααΌαα
ααα½αααααΎαααΆααααααααααααΆααααΆααΆαααΆααααΆαααα½αα ALTER TABLE ... SET (parallel_workers = N
).
α ααα»α’αααΈααΆαααΆααααΎαααΆααααΆαααΆα‘αααα·αααααΌαααΆαααααΎ?
ααααααααΈααΎαααααΈααΆαααΉααααααΉαααααα ααΆααααΆαααΆααααα½ααα·αα·αααααααααααααα
parallel_setup_cost
parallel_tuple_cost
Nested Loop Joins
PostgreSQL 9.6+ ΠΌΠΎΠΆΠ΅Ρ Π²ΡΠΏΠΎΠ»Π½ΡΡΡ Π²Π»ΠΎΠΆΠ΅Π½Π½ΡΠ΅ ΡΠΈΠΊΠ»Ρ ΠΏΠ°ΡΠ°Π»Π»Π΅Π»ΡΠ½ΠΎ β ΡΡΠΎ ΠΏΡΠΎΡΡΠ°Ρ ΠΎΠΏΠ΅ΡΠ°ΡΠΈΡ.
explain (costs off) select c_custkey, count(o_orderkey)
from customer left outer join orders on
c_custkey = o_custkey and o_comment not like '%special%deposits%'
group by c_custkey;
QUERY PLAN
--------------------------------------------------------------------------------------
Finalize GroupAggregate
Group Key: customer.c_custkey
-> Gather Merge
Workers Planned: 4
-> Partial GroupAggregate
Group Key: customer.c_custkey
-> Nested Loop Left Join
-> Parallel Index Only Scan using customer_pkey on customer
-> Index Scan using idx_orders_custkey on orders
Index Cond: (customer.c_custkey = o_custkey)
Filter: ((o_comment)::text !~~ '%special%deposits%'::text)
ααΆααααααΌααααα»αααΎαα‘αΎααα
ααααΆααααΆαα
α»αααααα ααΌα
αααα Nested Loop Left Join ααΊααΆααααα·ααααα·ααΆαααααααααΆα Parallel Index Only Scan ααααΌαααΆαααααΆααααα
αααα»ααααα 10α ααΆααααΎαααΆαααααααααΉαααΆααααααααααααααΆαααΆα‘ααα αααααααα c_custkey = o_custkey
α’αΆαααΆααααααΆαα·ααα½ααααα»ααα½ααα½αα’αα·αα·ααα ααΌα
ααααααΆαα·αααααααααΆααα
Hash α αΌααα½α
ααααΎαααΆααααααααααααα·αα½αααααααΎαααΆααΆα hash ααααΆαααααα½ααα αΌαααα PostgreSQL 11. α αΎαααααα·αααΎααΆαααααΎαααΆαααΎαααΈαα½αααα ααααΎαααΆαααΉααα·ααααααΎαα‘αΎαααα αα αααα»αααααααααΈ ααΆααΆα hash ααααΌαααΆαα αααααααα ααααΎαααΆααααααααα·αα½ααα’αΆα ααααΎ WORK_MEM ααΎααααΈαααααΎαααΆααΆααααααΆα
select
l_shipmode,
sum(case
when o_orderpriority = '1-URGENT'
or o_orderpriority = '2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT'
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,
lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('MAIL', 'AIR')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1996-01-01'
and l_receiptdate < date '1996-01-01' + interval '1' year
group by
l_shipmode
order by
l_shipmode
LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1964755.66..1964961.44 rows=1 width=27) (actual time=7579.592..7922.997 rows=1 loops=1)
-> Finalize GroupAggregate (cost=1964755.66..1966196.11 rows=7 width=27) (actual time=7579.590..7579.591 rows=1 loops=1)
Group Key: lineitem.l_shipmode
-> Gather Merge (cost=1964755.66..1966195.83 rows=28 width=27) (actual time=7559.593..7922.319 rows=6 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=1963755.61..1965192.44 rows=7 width=27) (actual time=7548.103..7564.592 rows=2 loops=5)
Group Key: lineitem.l_shipmode
-> Sort (cost=1963755.61..1963935.20 rows=71838 width=27) (actual time=7530.280..7539.688 rows=62519 loops=5)
Sort Key: lineitem.l_shipmode
Sort Method: external merge Disk: 2304kB
Worker 0: Sort Method: external merge Disk: 2064kB
Worker 1: Sort Method: external merge Disk: 2384kB
Worker 2: Sort Method: external merge Disk: 2264kB
Worker 3: Sort Method: external merge Disk: 2336kB
-> Parallel Hash Join (cost=382571.01..1957960.99 rows=71838 width=27) (actual time=7036.917..7499.692 rows=62519 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..1552386.40 rows=71838 width=19) (actual time=0.583..4901.063 rows=62519 loops=5)
Filter: ((l_shipmode = ANY ('{MAIL,AIR}'::bpchar[])) AND (l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1996-01-01'::date) AND (l_receiptdate < '1997-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 11934691
-> Parallel Hash (cost=313722.45..313722.45 rows=3750045 width=20) (actual time=2011.518..2011.518 rows=3000000 loops=5)
Buckets: 65536 Batches: 256 Memory Usage: 3840kB
-> Parallel Seq Scan on orders (cost=0.00..313722.45 rows=3750045 width=20) (actual time=0.029..995.948 rows=3000000 loops=5)
Planning Time: 0.977 ms
Execution Time: 7923.770 ms
αααα½αααΈ 12 ααΈ TPC-H αααα αΆααααΆαα αααΆααααΌαααΆαααααΆαααααααΆαααΆαααΆα‘ααα ααααΎαααΆααααααααα·αα½αααα½αα αααααααααΆααααααΎαααΆααΆααααααΆαα½ααα½αα
αααα αΌαα αΌαααααΆ
ααΆααα½ααααα αΌαααααΆααααΆααα½ααααα αΌαααααΆααΊαα·αααααααααΆαα αααα»αααααααΆαα·α αα»αααΆααααααααα·αααΎαααααΆααα αΆαα α»αααααααααααα½α - ααΆαα ααα’αΆα ααααΎαααΆαααααααααΆα
-- Query 2 from TPC-H
explain (costs off) select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from part, supplier, partsupp, nation, region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 36
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AMERICA'
and ps_supplycost = (
select
min(ps_supplycost)
from partsupp, supplier, nation, region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'AMERICA'
)
order by s_acctbal desc, n_name, s_name, p_partkey
LIMIT 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Limit
-> Sort
Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
-> Merge Join
Merge Cond: (part.p_partkey = partsupp.ps_partkey)
Join Filter: (partsupp.ps_supplycost = (SubPlan 1))
-> Gather Merge
Workers Planned: 4
-> Parallel Index Scan using <strong>part_pkey</strong> on part
Filter: (((p_type)::text ~~ '%BRASS'::text) AND (p_size = 36))
-> Materialize
-> Sort
Sort Key: partsupp.ps_partkey
-> Nested Loop
-> Nested Loop
Join Filter: (nation.n_regionkey = region.r_regionkey)
-> Seq Scan on region
Filter: (r_name = 'AMERICA'::bpchar)
-> Hash Join
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan on supplier
-> Hash
-> Seq Scan on nation
-> Index Scan using idx_partsupp_suppkey on partsupp
Index Cond: (ps_suppkey = supplier.s_suppkey)
SubPlan 1
-> Aggregate
-> Nested Loop
Join Filter: (nation_1.n_regionkey = region_1.r_regionkey)
-> Seq Scan on region region_1
Filter: (r_name = 'AMERICA'::bpchar)
-> Nested Loop
-> Nested Loop
-> Index Scan using idx_partsupp_partkey on partsupp partsupp_1
Index Cond: (part.p_partkey = ps_partkey)
-> Index Scan using supplier_pkey on supplier supplier_1
Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
-> Index Scan using nation_pkey on nation nation_1
Index Cond: (n_nationkey = supplier_1.s_nationkey)
ααααΆαα "αααα
αΌαααααΆ" ααΆαααΈααΆαααα
ααΆαααΎ "αααααΌααααα»ααααα
αΌαααααΆ" α ααΌα
ααααααΆααα½ααααα
αΌαααααΆαα·αααααΎααααΎαααΆααααΆαααΆα‘ααααα ααα»ααααααααΆαα "Parallel Index Scan" αα
αααα½αααΆαα½αααααααααα part_pkey
.
ααΆααααααΆααααΆαααααα
αα
αααα»α PostgreSQL 11
tpch=# set enable_partitionwise_join=t;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
QUERY PLAN
---------------------------------------------------
Append
-> Hash Join
Hash Cond: (t2.b = t1.a)
-> Seq Scan on prt2_p1 t2
Filter: ((b >= 0) AND (b <= 10000))
-> Hash
-> Seq Scan on prt1_p1 t1
Filter: (b = 0)
-> Hash Join
Hash Cond: (t2_1.b = t1_1.a)
-> Seq Scan on prt2_p2 t2_1
Filter: ((b >= 0) AND (b <= 10000))
-> Hash
-> Seq Scan on prt1_p2 t1_1
Filter: (b = 0)
tpch=# set parallel_setup_cost = 1;
tpch=# set parallel_tuple_cost = 0.01;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
QUERY PLAN
-----------------------------------------------------------
Gather
Workers Planned: 4
-> Parallel Append
-> Parallel Hash Join
Hash Cond: (t2_1.b = t1_1.a)
-> Parallel Seq Scan on prt2_p2 t2_1
Filter: ((b >= 0) AND (b <= 10000))
-> Parallel Hash
-> Parallel Seq Scan on prt1_p2 t1_1
Filter: (b = 0)
-> Parallel Hash Join
Hash Cond: (t2.b = t1.a)
-> Parallel Seq Scan on prt2_p1 t2
Filter: ((b >= 0) AND (b <= 10000))
-> Parallel Hash
-> Parallel Seq Scan on prt1_p1 t1
Filter: (b = 0)
ααΏαα αααααΊααΆααΆααααααΆαααα αααα»ααααααααΊααααααααΆαα»αααααΆαααααααααΆαααααααΆαααα αααααααα
α§ααααααααααααΆαααΆα‘αα
ααΆαααααΎαααΆαααααααα ααα½α 2 αααα»αααααΎαααΆααα ααΈααα αααααΈααΆ 4 ααααΌαααΆαααΎααααααα
tpch=# explain (costs off) select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day union all select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '2000-12-01' - interval '105' day;
QUERY PLAN
------------------------------------------------------------------------------------------------
Gather
Workers Planned: 2
-> Parallel Append
-> Aggregate
-> Seq Scan on lineitem
Filter: (l_shipdate <= '2000-08-18 00:00:00'::timestamp without time zone)
-> Aggregate
-> Seq Scan on lineitem lineitem_1
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
α’αααααααΆαααααα»α
- WORK_MEM αααααα’αααα αα αΆααααα»ααα½αααααΎαααΆα αα·ααααααααΆααααααΆαααα½αααα work_mem ααααΎαααΆα ααΆααααααΆαα = ααΆαα αα αΆαα αααΎαα
- ααΎααααΎαααΆαααααααααα»ααααΆαααΆααααααααααα·ααΈααααα·ααααα·ααΉαααααΎαααααΆααααααΎαααΆαααααααααΆααΈαααααΆααmax_parallel_workers_per_gather
- αααααααΌαα ααα½αααα»αααααααΎαααΆααααααααααααα ααΉαα ααα½αααααΌααααΈααΈααΌαα ααΎαααΆαααΈααααmax_worker_processes
- ααΌα ααααΆ ααα»αααααααααΆααααααΎαααΆαααΆαααΆαααααααααΆαmax_parallel_workers
αααααα
α αΆααααΈαααα 9.6 ααααΎαααΆααααΆαααΆα‘ααα’αΆα ααααΎα±αααααααΎαα‘αΎααααΆαααααΆααααΌαααααΎαααΆααααααα½ααααα»αααααΆααααααααααα½αα¬αα·αα·ααααααΆα αααΎαα αα αααα»α PostgreSQL 10 ααααΎαααΆααααΆαααΆα‘ααααααΌαααΆαααΎαααΆαααααΆαααΎαα α αα αΆαααΆααααΌααα·αααΆαα ααΎαααΆαααΈααααααααΆαααααα»αααΆαααΆα OLTP ααα ααΆααααααααΆαααααΆααααααα α¬ααΆαααααααα·αα·ααααααααΎααααΆααααααΆαα αααΎαα ααααα·αααΎα’ααααα·αααααΎαααΆααααΆαααΆαααααΎαααα»ααα·ααααααααΆααααΌααα α’αααα’αΆα αααααα’ααααΎαααΆααααα½ααααααααΆαααααααααααα·αα·αααααααααΆαα α¬ααααΎααΆααααα ααααααΉαααααΌαα
ααα ααααΈααα
https://www.postgresql.org/docs/11/how-parallel-query-works.html https://www.postgresql.org/docs/11/parallel-plans.html http://ashutoshpg.blogspot.com/2017/12/partition-wise-joins-divide-and-conquer.html http://rhaas.blogspot.com/2016/04/postgresql-96-with-parallel-query-vs.html http://amitkapila16.blogspot.com/2015/11/parallel-sequential-scans-in-play.html https://write-skew.blogspot.com/2018/01/parallel-hash-for-postgresql.html http://rhaas.blogspot.com/2017/03/parallel-query-v2.html https://blog.2ndquadrant.com/parallel-monster-benchmark/ https://blog.2ndquadrant.com/parallel-aggregate/ https://www.depesz.com/2018/02/12/waiting-for-postgresql-11-support-parallel-btree-index-builds/ ααΆαααααααααΆαα αααα»α PostgreSQL α‘α‘
ααααα: www.habr.com