ืคึผืจืึธื”ืึธืกื˜ืขืจ > ื‘ืœืึธื’ > ืึทื“ืžื™ื ื™ืกื˜ืจืึทืฆื™ืข > ืคึผืึทืจืึทืœืขืœ ืคึฟืจืื’ืŸ ืื™ืŸ PostgreSQL

ืคึผืึทืจืึทืœืขืœ ืคึฟืจืื’ืŸ ืื™ืŸ PostgreSQL

ืคึผืึทืจืึทืœืขืœ ืคึฟืจืื’ืŸ ืื™ืŸ PostgreSQL
ืžืึธื“ืขืจืŸ ืงืคึผื•ืก ื”ืึธื‘ืŸ ืึท ืคึผืœืึทืฅ ืคื•ืŸ ืงืึธืจืขืก. ืคึฟืึทืจ ื™ืึธืจืŸ, ืึทืคึผืœืึทืงื™ื™ืฉืึทื ื– ื”ืึธื‘ืŸ ืฉื™ืงื˜ ืงื•ื•ื™ืจื™ื– ืฆื• ื“ืึทื˜ืึทื‘ื™ื™ืกื™ื– ืื™ืŸ ืคึผืึทืจืึทืœืขืœ. ืื•ื™ื‘ ืขืก ืื™ื– ืึท ื‘ืึทืจื™ื›ื˜ ืึธื ืคึฟืจืขื’ ืื•ื™ืฃ ืงื™ื™ืคืœ ืจืึธื•ื– ืื™ืŸ ืึท ื˜ื™ืฉ, ืขืก ืœื•ื™ืคื˜ ืคืึทืกื˜ืขืจ ื•ื•ืขืŸ ื ื™ืฆืŸ ืงื™ื™ืคืœ ืงืคึผื•ืก, ืื•ืŸ PostgreSQL ืื™ื– ื‘ื™ื›ื•ืœืช ืฆื• ื˜ืึธืŸ ื“ืึธืก ื–ื™ื ื˜ ื•ื•ืขืจืกื™ืข 9.6.

ืขืก ื”ืึธื˜ ื’ืขื“ื•ื™ืขืจื˜ 3 ื™ืึธืจ ืฆื• ื™ื ืกื˜ืจื•ืžืขื ื˜ ื“ื™ ืคึผืึทืจืึทืœืขืœ ืึธื ืคึฟืจืขื’ ืฉื˜ืจื™ืš - ืžื™ืจ ื”ืึธื‘ืŸ ืฆื• ืจื™ืจื™ื™ื˜ ื“ื™ ืงืึธื“ ืื™ืŸ ืคืึทืจืฉื™ื“ืขื ืข ืกื˜ืึทื’ืขืก ืคื•ืŸ ืึธื ืคึฟืจืขื’ ื“ื•ืจื›ืคื™ืจื•ื ื’. PostgreSQL 9.6 ื™ื ื˜ืจืึธื•ื“ื•ืกื˜ ื™ื ืคืจืึทืกื˜ืจืึทืงื˜ืฉืขืจ ืฆื• ืคึฟืึทืจื‘ืขืกืขืจืŸ ื“ื™ ืงืึธื“ ื•ื•ื™ื™ึทื˜ืขืจ. ืื™ืŸ ืกืึทื‘ืกืึทืงื•ื•ืึทื ื˜ ื•ื•ืขืจืกื™ืขืก, ืื ื“ืขืจืข ื˜ื™ื™ืคึผืก ืคื•ืŸ ืงื•ื•ื™ืจื™ื– ื–ืขื ืขืŸ ืขืงืกืึทืงื™ื•ื˜ืึทื“ ืื™ืŸ ืคึผืึทืจืึทืœืขืœ.

ืจื™ืกื˜ืจื™ืงืฉืึทื ื–

  • ื“ื• ื–ืืœืกื˜ ื ื™ืฉื˜ ื’ืขื‘ืŸ ืคึผืึทืจืึทืœืขืœ ื“ื•ืจื›ืคื™ืจื•ื ื’ ืื•ื™ื‘ ืึทืœืข ืงืึธืจืขืก ื–ืขื ืขืŸ ืฉื•ื™ืŸ ืคืึทืจื ื•ืžืขืŸ, ืึทื ื“ืขืจืฉ ืื ื“ืขืจืข ืจื™ืงื•ื•ืขืก ื•ื•ืขื˜ ืคึผืึทืžืขืœืขืš ืึทืจืึธืคึผ.
  • ืจื•ื‘ึฟ ื™ืžืคึผืึธืจื˜ืึทื ื˜ืœื™, ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’ ืžื™ื˜ ื”ื•ื™ืš WORK_MEM ื•ื•ืึทืœื•ืขืก ื ื™ืฆื˜ ืึท ืคึผืœืึทืฅ ืคื•ืŸ ื–ื›ึผืจื•ืŸ - ื™ืขื“ืขืจ ื”ืึทืฉ ืคืึทืจื‘ื™ื ื“ืŸ ืึธื“ืขืจ ืกืึธืจื˜ ื ืขืžื˜ ื•ื•ืึธืจืง_ืžืขื ื–ื›ึผืจื•ืŸ.
  • ื ื™ื“ืขืจื™ืง ืœื™ื™ื˜ืึทื ืกื™ OLTP ืงื•ื•ื™ืจื™ื– ืงืขื ืขืŸ ื ื™ื˜ ื–ื™ื™ืŸ ืึทืงืกืขืœืขืจื™ื™ื˜ื™ื“ ื“ื•ืจืš ืคึผืึทืจืึทืœืขืœ ื“ื•ืจื›ืคื™ืจื•ื ื’. ืื•ืŸ ืื•ื™ื‘ ื“ื™ ืึธื ืคึฟืจืขื’ ืงืขืจื˜ ืื™ื™ืŸ ืจื•ื“ืขืจืŸ, ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’ ื•ื•ืขื˜ ื ืึธืจ ืคึผืึทืžืขืœืขืš ืขืก ืึทืจืึธืคึผ.
  • ื“ืขื•ื•ืขืœืึธืคึผืขืจืก ืœื™ื‘ ืฆื• ื ื•ืฆืŸ ื“ื™ TPC-H ื‘ืขื ื˜ืฉืžืึทืจืง. ืืคึฟืฉืจ ืื™ืจ ื”ืึธื‘ืŸ ืขื ืœืขืš ืคึฟืจืื’ืŸ ืคึฟืึทืจ ืฉืœื™ื™ืžืขืกื“ื™ืง ืคึผืึทืจืึทืœืขืœ ื“ื•ืจื›ืคื™ืจื•ื ื’.
  • ื‘ืœื•ื™ื– ืกืขืœืขืงื˜ ืงื•ื•ื™ืจื™ื– ืึธืŸ ืคึผืจื™ื“ื™ืงืึทื˜ ืœืึทืงื™ื ื’ ื–ืขื ืขืŸ ืขืงืกืึทืงื™ื•ื˜ืึทื“ ืื™ืŸ ืคึผืึทืจืึทืœืขืœ.
  • ืžืืœ ื’ืขื”ืขืจื™ืง ื™ื ื“ืขืงืกื™ื ื’ ืื™ื– ื‘ืขืกืขืจ ื•ื•ื™ ืกืึทืงื•ื•ืขื ื˜ืฉืึทืœ ื˜ื™ืฉ ืกืงืึทื ื™ื ื’ ืื™ืŸ ืคึผืึทืจืึทืœืขืœ ืžืึธื“ืข.
  • ืคึผืึธื•ื–ื™ื ื’ ืงื•ื•ื™ืจื™ื– ืื•ืŸ ืงื•ืจืกืึธืจืก ื–ืขื ืขืŸ ื ื™ืฉื˜ ื’ืขืฉื˜ื™ืฆื˜.
  • ืคึฟืขื ืฆื˜ืขืจ ืคืึทื ื’ืงืฉืึทื ื– ืื•ืŸ ืึธืจื“ืขืจื“ ื’ืขืžื™ื™ื ื–ืึทื ืคืึทื ื’ืงืฉืึทื ื– ื–ืขื ืขืŸ ื ื™ืฉื˜ ืคึผืึทืจืึทืœืขืœ.
  • ืื™ืจ ื˜ืึธืŸ ื ื™ื˜ ื’ืขื•ื•ื™ื ืขืŸ ืขืคึผืขืก ืื™ืŸ ื“ื™ I/O ื•ื•ืขืจืงืœืึธื•ื“.
  • ืขืก ื–ืขื ืขืŸ ืงื™ื™ืŸ ืคึผืึทืจืึทืœืขืœ ืกืึธืจื˜ื™ื ื’ ืึทืœื’ืขืจื™ื“ืึทืžื–. ืึธื‘ืขืจ ืงื•ื•ื™ืจื™ื– ืžื™ื˜ ืกืึธืจืฅ ืงืขื ืขืŸ ื–ื™ื™ืŸ ืขืงืกืึทืงื™ื•ื˜ืึทื“ ืื™ืŸ ืคึผืึทืจืึทืœืขืœ ืื™ืŸ ืขื˜ืœืขื›ืข ืึทืกืคึผืขืงืฅ.
  • ืคืึทืจื‘ื™ื™ึทื˜ืŸ CTE (ืžื™ื˜ ...) ืžื™ื˜ ืึท ื ืขืกื˜ืขื“ SELECT ืฆื• ื’ืขื‘ืŸ ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’.
  • ื“ืจื™ื˜-ืคึผืึทืจื˜ื™ื™ ื“ืึทื˜ืŸ ืจืึทืคึผืขืจื– ื˜ืึธืŸ ื ื™ื˜ ื ืึธืš ืฉื˜ื™ืฆืŸ ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’ (ืึธื‘ืขืจ ื–ื™ื™ ืงืขืŸ!)
  • ืคื•ืœ ืึทื•ื˜ืขืจ ืคืึทืจื‘ื™ื ื“ืŸ ืื™ื– ื ื™ืฉื˜ ื’ืขืฉื˜ื™ืฆื˜.
  • max_rows ื“ื™ืกื™ื™ื‘ืึทืœื– ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’.
  • ืื•ื™ื‘ ืึท ืึธื ืคึฟืจืขื’ ื”ืื˜ ืึท ืคึฟื•ื ืงืฆื™ืข ื•ื•ืึธืก ืื™ื– ื ื™ืฉื˜ ืื ื’ืขืฆื™ื™ื›ื ื˜ PARALLEL SAFE, ืขืก ื•ื•ืขื˜ ื–ื™ื™ืŸ ืื™ื™ืŸ ื˜ืจืขื“ื™ื“.
  • ื“ื™ SERIALIZABLE ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ ืืคื’ืขื–ื•ื ื“ืขืจื˜ืงื™ื™ื˜ ืžื“ืจื’ื” ื“ื™ืกื™ื™ื‘ืึทืœื– ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’.

ื˜ืขืกื˜ ืกื•ื•ื™ื•ื•ืข

PostgreSQL ื“ืขื•ื•ืขืœืึธืคึผืขืจืก ื’ืขืคืจื•ื•ื•ื˜ ืฆื• ืจืขื“ื•ืฆื™ืจืŸ ื“ื™ ืขื ื˜ืคืขืจ ืฆื™ื™ื˜ ืคื•ืŸ TPC-H ื‘ืขื ื˜ืฉืžืึทืจืง ืคึฟืจืื’ืŸ. ืืจืืคืงืืคื™ืข ื“ื™ ื‘ืขื ื˜ืฉืžืึทืจืง ืื•ืŸ ืึทื“ืึทืคึผื˜ ืขืก ืฆื• PostgreSQL. ื“ืึธืก ืื™ื– ืึทืŸ ืึทื ืึทืคื™ืฉืึทืœ ื ื•ืฆืŸ ืคื•ืŸ ื“ื™ TPC-H ื‘ืขื ื˜ืฉืžืึทืจืง - ื ื™ื˜ ืคึฟืึทืจ ื“ื™ื™ื˜ืึทื‘ื™ื™ืก ืึธื“ืขืจ ื™ื™ึทื–ื ื•ื•ืึทืจื’ ืคืึทืจื’ืœื™ื™ึทืš.

  1. ืืจืืคืงืืคื™ืข TPC-H_Tools_v2.17.3.zip (ืึธื“ืขืจ ื ื™ื™ึทืขืจ ื•ื•ืขืจืกื™ืข) ืคึฟื•ืŸ TPC ืึทื•ื•ืขืง.
  2. ืจืขื ืึทืžืข makefile.suite ืฆื• Makefile ืื•ืŸ ื˜ื•ื™ืฉืŸ ื•ื•ื™ ื“ื™ืกืงืจื™ื™ื‘ื“ ื“ืึธ: https://github.com/tvondra/pg_tpch . ืงืึทืžืคึผื™ื™ืœ ื“ื™ ืงืึธื“ ืžื™ื˜ ื“ื™ ืžืึทื›ืŸ ื‘ืึทืคึฟืขืœ.
  3. ืฉืึทืคึฟืŸ ื“ืึทื˜ืŸ: ./dbgen -s 10 ืงืจื™ื™ื™ืฅ ืึท 23 ื’ื™ื’ืื‘ื™ื™ื˜ ื“ืึทื˜ืึทื‘ื™ื™ืก. ื“ืึธืก ืื™ื– ื’ืขื ื•ื’ ืฆื• ื–ืขืŸ ื“ื™ ื—ื™ืœื•ืง ืื™ืŸ ื“ื™ ืคืึธืจืฉื˜ืขืœื•ื ื’ ืคื•ืŸ ืคึผืึทืจืึทืœืขืœ ืื•ืŸ ื ื™ื˜-ืคึผืึทืจืึทืœืขืœ ืงื•ื•ื™ืจื™ื–.
  4. ื’ืขืจ ื˜ืขืงืขืก tbl ะฒ csv ั for ะธ sed.
  5. ืงืœืึธื•ืŸ ื“ื™ ืจื™ืคึผืึทื–ืึทื˜ืึธืจื™ pg_tpch ืื•ืŸ ื ืึธื›ืžืึทื›ืŸ ื“ื™ ื˜ืขืงืขืก csv ะฒ pg_tpch/dss/data.
  6. ืฉืึทืคึฟืŸ ืงื•ื•ื™ืจื™ื– ืžื™ื˜ ืึท ื‘ืึทืคึฟืขืœ qgen.
  7. ืœืึธื“ืŸ ื“ืึทื˜ืŸ ืื™ืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืžื™ื˜ ื“ื™ ื‘ืึทืคึฟืขืœ ./tpch.sh.

ืคึผืึทืจืึทืœืขืœ ืกืึทืงื•ื•ืขื ื˜ืฉืึทืœ ืกืงืึทื ื™ื ื’

ืขืก ืงืขืŸ ื–ื™ื™ืŸ ืคืึทืกื˜ืขืจ ื ื™ื˜ ื•ื•ื™ื™ึทืœ ืคื•ืŸ ืคึผืึทืจืึทืœืขืœ ืœื™ื™ืขื ืขืŸ, ืึธื‘ืขืจ ื•ื•ื™ื™ึทืœ ื“ื™ ื“ืึทื˜ืŸ ื–ืขื ืขืŸ ืคืึทืจืฉืคึผืจื™ื™ื˜ืŸ ืื™ื‘ืขืจ ืคื™ืœืข ืงืคึผื• ืงืึธืจืขืก. ืื™ืŸ ืžืึธื“ืขืจืŸ ืึธืคึผืขืจื™ื™ื˜ื™ื ื’ ืกื™ืกื˜ืขืžืขืŸ, PostgreSQL ื“ืึทื˜ืŸ ื˜ืขืงืขืก ื–ืขื ืขืŸ ื’ืขื–ื•ื ื˜ ืงืึทืฉื˜. ืžื™ื˜ ืœื™ื™ืขื ืขืŸ ืคืึธืจื•ื™ืก, ืขืก ืื™ื– ืžืขื’ืœืขืš ืฆื• ื‘ืึทืงื•ืžืขืŸ ืึท ื’ืจืขืกืขืจืข ื‘ืœืึธืง ืคื•ืŸ ืกื˜ืึธืจื™ื“ื–ืฉ ื•ื•ื™ ื“ื™ PG ื“ื™ื™ืžืึทืŸ ืจื™ืงื•ื•ืขืก. ื“ืขืจื™ื‘ืขืจ, ืึธื ืคึฟืจืขื’ ืคืึธืจืฉื˜ืขืœื•ื ื’ ืื™ื– ื ื™ืฉื˜ ืœื™ืžื™ื˜ืขื“ ื“ื•ืจืš ื“ื™ืกืง I / O. ืขืก ืงืึทื ืกื•ืžื– ืงืคึผื• ืกื™ื™ืงืึทืœื– ืฆื•:

  • ืœื™ื™ืขื ืขืŸ ืจืึธื•ื– ืื™ื™ื ืขืจ ืื™ืŸ ืึท ืฆื™ื™ึทื˜ ืคื•ืŸ ื˜ื™ืฉ ื‘ืœืขื˜ืขืจ;
  • ืคืึทืจื’ืœื™ื™ึทื›ืŸ ืฉื˜ืจื™ืงืœ ื•ื•ืึทืœื•ืขืก ืื•ืŸ ื˜ื ืึธื™ื 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

ืคึผืึทืจืึทืœืขืœ ืึทื’ื’ืจืขื’ืึทื˜ื™ืึธืŸ

ื“ื™ ืคึผืึทืจืึทืœืขืœ ืกืขืง ืกืงืึทืŸ ื ืึธื“ืข ื˜ืจืื’ื˜ ืจืึธื•ื– ืคึฟืึทืจ ืคึผืึทืจื˜ื™ื™ื™ืฉ ืึทื’ื’ืจืขื’ืึทื˜ื™ืึธืŸ. ื“ื™ "ืคึผืึทืจื˜ื™ืึทืœ ืึทื’ื’ืจืขื’ืึทื˜ืข" ื ืึธื“ืข ื˜ืจื™ืžื– ื“ื™ ืฉื•ืจื•ืช ื ื™ืฆืŸ SUM(). ืื™ืŸ ื“ื™ ืกื•ืฃ, ื“ื™ 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

ื•ื•ืืก ื’ื™ื™ื˜ ื“ื ืคืืจ? ืขืก ื–ืขื ืขืŸ ื’ืขื•ื•ืขืŸ 2 ืžืืœ ืžืขืจ ืึทืจื‘ืขื˜ ืคึผืจืึทืกืขืกืึทื–, ืื•ืŸ ื“ื™ ื‘ืงืฉื” ืื™ื– ื’ืขื•ื•ืขืŸ ื‘ืœื•ื™ื– 1,6599 ืžืืœ ืคืึทืกื˜ืขืจ. ื“ื™ ื—ืฉื‘ื•ื ื•ืช ื–ืขื ืขืŸ ืื™ื ื˜ืขืจืขืกืื ื˜. ืžื™ืจ ื”ืื‘ืŸ 2 ืึทืจื‘ืขื˜ืขืจ ืคึผืจืึทืกืขืกืึทื– ืื•ืŸ 1 ืคื™ืจืขืจ. ื ืึธืš ื“ื™ ืขื ื“ืขืจื•ื ื’ ืขืก ืื™ื– ื’ืขื•ื•ืืจืŸ 4+1.

ืื•ื ื“ื–ืขืจ ืžืึทืงืกื™ืžื•ื ื’ื™ื›ืงื™ื™ึทื˜ ืคื•ืŸ ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’: 5/3 = 1,66 (6) ืžืืœ.

ื•ื•ื™ ื˜ื•ื˜ ืขืก ืึทืจื‘ืขื˜?

ื“ื™ ืคึผืจืึทืกืขืกืึทื–

ื“ื™ ื“ื•ืจื›ืคื™ืจื•ื ื’ ืคื•ืŸ ื“ื™ ื‘ืขื˜ืŸ ืกื˜ืึทืจืฅ ืฉื˜ืขื ื“ื™ืง ืžื™ื˜ ื“ื™ ืœื™ื“ื™ื ื’ ืคึผืจืึธืฆืขืก. ื“ืขืจ ืคื™ืจืขืจ ื˜ื•ื˜ ืึทืœืฅ ื ื™ื˜-ืคึผืึทืจืึทืœืขืœ ืื•ืŸ ืขื˜ืœืขื›ืข ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’. ืื ื“ืขืจืข ืคึผืจืึทืกืขืกืึทื– ื•ื•ืึธืก ื“ื•ืจื›ืคื™ืจืŸ ื“ื™ ื–ืขืœื‘ืข ืจื™ืงื•ื•ืขืก ื–ืขื ืขืŸ ื’ืขืจื•ืคืŸ ืึทืจื‘ืขื˜ืขืจ ืคึผืจืึทืกืขืกืึทื–. ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’ ื ื™ืฆื˜ ื™ื ืคืจืึทืกื˜ืจืึทืงื˜ืฉืขืจ ื“ื™ื ืึทืžื™ืฉ ื”ื™ื ื˜ืขืจื’ืจื•ื ื˜ ืึทืจื‘ืขื˜ืขืจ ืคึผืจืึทืกืขืกืึทื– (ืคึฟื•ืŸ ื•ื•ืขืจืกื™ืข 9.4). ื–ื™ื ื˜ ืื ื“ืขืจืข ืคึผืึทืจืฅ ืคื•ืŸ PostgreSQL ื ื•ืฆืŸ ืคึผืจืึทืกืขืกืึทื– ืืœื ื•ื•ื™ ืคึฟืขื“ืขื, ืึท ืึธื ืคึฟืจืขื’ ืžื™ื˜ 3 ืึทืจื‘ืขื˜ืขืจ ืคึผืจืึทืกืขืกืึทื– ืงืขืŸ ื–ื™ื™ืŸ 4 ืžืืœ ืคืึทืกื˜ืขืจ ื•ื•ื™ ื˜ืจืื“ื™ืฆื™ืื ืขืœืŸ ืคึผืจืึทืกืขืกื™ื ื’.

ื™ื ื˜ืขืจืึทืงื˜ื™ืึธืŸ

ืึทืจื‘ืขื˜ืขืจ ืคึผืจืึทืกืขืกืึทื– ื™ื‘ืขืจื’ืขื‘ืŸ ืžื™ื˜ ื“ื™ ืคื™ืจืขืจ ื“ื•ืจืš ืึท ืึธื ื–ืึธื’ ืจื™ื™ (ื‘ืื–ื™ืจื˜ ืื•ื™ืฃ ืฉืขืจื“ ื–ื›ึผืจื•ืŸ). ื™ืขื“ืขืจ ืคึผืจืึธืฆืขืก ื”ืื˜ 2 ืงื™ื•ื–: ืคึฟืึทืจ ืขืจืจืึธืจืก ืื•ืŸ ืคึฟืึทืจ ื˜ื•ืคึผืœืขืก.

ื•ื•ื™ ืคื™ืœืข ื•ื•ืึธืจืงืคืœืึธื•ื•ืก ื–ืขื ืขืŸ ื“ืืจืฃ?

ื“ื™ ืžื™ื ื™ืžื•ื ืฉื™ืขื•ืจ ืื™ื– ืกืคึผืขืกื™ืคื™ืขื“ ื“ื•ืจืš ื“ื™ ืคึผืึทืจืึทืžืขื˜ืขืจ 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: ืงืึธืžื•ื ื™ืงืึทืฆื™ืข ืฆื•ื•ื™ืฉืŸ ื“ื™ ืคื™ืจืขืจ ืื•ืŸ ื˜ื•ืขืจืก ืงืขื ืขืŸ ื–ื™ื™ืŸ ื“ื™ืœื™ื™ื“ ืื™ืŸ ืคึผืจืึธืคึผืึธืจืฆื™ืข ืฆื• ื“ื™ ื ื•ืžืขืจ ืคื•ืŸ ื˜ื•ืคึผืœืขืก ืคื•ืŸ ืึทืจื‘ืขื˜ ืคึผืจืึทืกืขืกืึทื–. ื“ืขื ืคึผืึทืจืึทืžืขื˜ืขืจ ืงืึทืœืงื™ืึทืœื™ื™ืฅ ื“ื™ ืคึผืจื™ื™ึทื– ืคื•ืŸ ื“ืึทื˜ืŸ ื•ื•ืขืงืกืœ.

ื ืขืกื˜ืขื“ ืœื•ืคึผ ื“ื–ืฉืึธื™ื ืก

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 ืื™ื– ืึท ืคึผืึทืจืึทืœืขืœ ืึธืคึผืขืจืึทืฆื™ืข. ืคึผืึทืจืึทืœืขืœ ืื™ื ื“ืขืงืก ื‘ืœื•ื™ื– ืกืงืึทืŸ ืื™ื– ื‘ืึทืงืขื ืขื  ื‘ืœื•ื™ื– ืื™ืŸ ื•ื•ืขืจืกื™ืข 10. ืขืก ืึทืจื‘ืขื˜ ืกื™ืžืึทืœืขืจืœื™ ืฆื• ืคึผืึทืจืึทืœืขืœ ืกื™ืจื™ืึทืœ ืกืงืึทื ื™ื ื’. ืฆื•ืฉื˜ืึทื ื“ c_custkey = o_custkey ืœื™ื™ืขื ื˜ ืื™ื™ืŸ ืกื“ืจ ืคึผืขืจ ืงืœื™ืขื ื˜ ืฉื˜ืจื™ืงืœ. ืึทื–ื•ื™ ืขืก ืื™ื– ื ื™ืฉื˜ ืคึผืึทืจืึทืœืขืœ.

ื”ืึทืฉ ืคืึทืจื‘ื™ื ื“ืŸ

ื™ืขื“ืขืจ ืึทืจื‘ืขื˜ืขืจ ืคึผืจืึธืฆืขืก ืงืจื™ื™ื™ืฅ ื–ื™ื™ืŸ ืื™ื™ื’ืขื ืข ื”ืึทืฉ ื˜ื™ืฉ ื‘ื™ื– PostgreSQL 11. ืื•ืŸ ืื•ื™ื‘ ืขืก ื–ืขื ืขืŸ ืžืขืจ ื•ื•ื™ ืคื™ืจ ืคื•ืŸ ื“ื™ ืคึผืจืึทืกืขืกืึทื–, ื“ื™ ืคืึธืจืฉื˜ืขืœื•ื ื’ ื•ื•ืขื˜ ื ื™ืฉื˜ ืคึฟืึทืจื‘ืขืกืขืจืŸ. ืื™ืŸ ื“ื™ ื ื™ื™ึทืข ื•ื•ืขืจืกื™ืข, ื“ื™ ื”ืึทืฉ ื˜ื™ืฉ ืื™ื– ืฉืขืจื“. ื™ืขื“ืขืจ ืึทืจื‘ืขื˜ืขืจ ืคึผืจืึธืฆืขืก ืงืขื ืขืŸ ื ื•ืฆืŸ 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)

ื“ื™ "ืžืขืจื“ื–ืฉ ื“ื–ืฉื•ื™ืŸ" ื ืึธื“ืข ืื™ื– ืœื™ื’ืŸ ืื•ื™ื‘ืŸ ื“ื™ "ืงืœื™ื™ึทื‘ืŸ ืฆื•ื ื•ื™ืคื’ื™ืกืŸ". ืึทื–ื•ื™ ืžืขืจื“ื–ืฉื™ื ื’ ื˜ื•ื˜ ื ื™ืฉื˜ ื ื•ืฆืŸ ืคึผืึทืจืึทืœืขืœ ืคึผืจืึทืกืขืกื™ื ื’. ืึธื‘ืขืจ ื“ื™ "ืคึผืึทืจืึทืœืขืœ ืื™ื ื“ืขืงืก ืกืงืึทืŸ" ื ืึธื“ืข ื ืึธืš ื”ืขืœืคึผืก ืžื™ื˜ ื“ื™ ืึธืคึผืฉื ื™ื˜ part_pkey.

ืงืฉืจ ื“ื•ืจืš ืกืขืงืฉืึทื ื–

ืื™ืŸ PostgreSQL 11 ืงืฉืจ ื“ื•ืจืš ืกืขืงืฉืึทื ื– ืคืึทืจืงืจื™ืคึผืœื˜ ื“ื•ืจืš ืคืขืœื™ืงื™ื™ึทื˜: ืขืก ื”ืื˜ ื–ื™ื™ืขืจ ื˜ื™ื™ึทืขืจ ืกืงืขื“ื–ืฉื•ืœื™ื ื’. ื˜ื™ืฉืŸ ืžื™ื˜ ืขื ืœืขืš ืคึผืึทืจื˜ื™ืฉืึทื ื™ื ื’ ืงืขื ืขืŸ ื–ื™ื™ืŸ ื“ื–ืฉื•ื™ื ื“ ืฆืขื˜ื™ื™ืœื•ื ื’ ื“ื•ืจืš ืฆืขื˜ื™ื™ืœื•ื ื’. ื“ืขืจ ื•ื•ืขื’ Postgres ื•ื•ืขื˜ ื ื•ืฆืŸ ืงืœืขื ืขืจืขืจ ื”ืึทืฉ ื˜ื™ืฉืŸ. ื™ืขื“ืขืจ ืงืฉืจ ืคื•ืŸ ืกืขืงืฉืึทื ื– ืงืขื ืขืŸ ื–ื™ื™ืŸ ืคึผืึทืจืึทืœืขืœ.

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)

ื“ื™ ื”ื•ื™ืคึผื˜ ื–ืึทืš ืื™ื– ืึทื– ื“ื™ ืงืฉืจ ืื™ืŸ ืกืขืงืฉืึทื ื– ืื™ื– ืคึผืึทืจืึทืœืขืœ ื‘ืœื•ื™ื– ืื•ื™ื‘ ื“ื™ ืกืขืงืฉืึทื ื– ื–ืขื ืขืŸ ื’ืจื•ื™ืก ื’ืขื ื•ื’.

ืคึผืึทืจืึทืœืขืœ ืึทืคึผืขื ื“

ืคึผืึทืจืึทืœืขืœ ืึทืคึผืขื ื“ ืงืขื ืขืŸ ื–ื™ื™ืŸ ื’ืขื•ื•ื™ื™ื ื˜ ืึทื ืฉื˜ืึธื˜ ืคื•ืŸ ืคืึทืจืฉื™ื“ืขื ืข ื‘ืœืึทืงืก ืื™ืŸ ืคืึทืจืฉื™ื“ืขื ืข ื•ื•ืึธืจืงืคืœืึธื•ื•ืก. ื“ืขื ื™ื•ื–ืฉืึทื•ื•ืึทืœื™ ื›ืึทืคึผืึทื ื– ืžื™ื˜ UNION ALL ืงื•ื•ื™ืจื™ื–. ื“ื™ ื›ื™ืกืึธืจืŸ ืื™ื– ื•ื•ื™ื™ื ื™ืงืขืจ ืคึผืึทืจืึทืœืขืœื™ื–ืึทื, ื•ื•ื™ื™ึทืœ ื™ืขื“ืขืจ ืึทืจื‘ืขื˜ืขืจ ืคึผืจืึธืฆืขืก ื‘ืœื•ื™ื– ืคึผืจืึทืกืขืกืึทื– 1 ื‘ืขื˜ืŸ.

ืขืก ื–ืขื ืขืŸ 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 ื•ื•ืขืจืงืœืึธื•ื“. ืกืึทืงื•ื•ืขื ื˜ืฉืึทืœ ืกืงืึทื ื– ืึธื“ืขืจ ืื™ื ื“ืขืงืก ืกืงืึทื ื– ืคืึทืจื ื•ืฆืŸ ืึท ืคึผืœืึทืฅ ืคื•ืŸ ืจืขืกื•ืจืกืŸ. ืื•ื™ื‘ ืื™ืจ ื˜ืึธืŸ ื ื™ื˜ ืœื•ื™ืคืŸ ืึท ื‘ืึทืจื™ื›ื˜ ืื•ื™ืฃ ื“ื™ ื’ืื ืฆืข ื“ืึทื˜ืึทืกืขื˜, ืื™ืจ ืงืขื ืขืŸ ืคึฟืึทืจื‘ืขืกืขืจืŸ ืึธื ืคึฟืจืขื’ ืคืึธืจืฉื˜ืขืœื•ื ื’ ื“ื•ืจืš ืคืฉื•ื˜ ืึทื“ื™ื ื’ ืคืขืœื ื“ื™ืง ื™ื ื“ืขืงืกื™ื– ืึธื“ืขืจ ื ื™ืฆืŸ ื’ืขื”ืขืจื™ืง ืคึผืึทืจื˜ื™ืฉืึทื ื™ื ื’.

ืจืขืคึฟืขืจืขื ืฆืŸ

ืžืงื•ืจ: www.habr.com

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’