āĻāĻ§ā§āĻ¨āĻŋāĻ CPU-āĻ¤ā§ āĻĒā§āĻ°āĻā§āĻ° āĻā§āĻ° āĻĨāĻžāĻā§āĨ¤ āĻŦāĻāĻ°ā§āĻ° āĻĒāĻ° āĻŦāĻāĻ° āĻ§āĻ°ā§, āĻ
ā§āĻ¯āĻžāĻĒā§āĻ˛āĻŋāĻā§āĻļāĻ¨āĻā§āĻ˛āĻŋ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛āĻāĻžāĻŦā§ āĻĄā§āĻāĻžāĻŦā§āĻ¸ā§ āĻĒā§āĻ°āĻļā§āĻ¨ āĻĒāĻžāĻ āĻžāĻā§āĻā§āĨ¤ āĻ¯āĻĻāĻŋ āĻāĻāĻŋ āĻāĻāĻāĻŋ āĻā§āĻŦāĻŋāĻ˛ā§āĻ° āĻāĻāĻžāĻ§āĻŋāĻ āĻ¸āĻžāĻ°āĻŋāĻ¤ā§ āĻāĻāĻāĻŋ āĻĒā§āĻ°āĻ¤āĻŋāĻŦā§āĻĻāĻ¨ā§āĻ° āĻĒā§āĻ°āĻļā§āĻ¨ āĻšāĻ¯āĻŧ, āĻ¤āĻŦā§ āĻāĻāĻžāĻ§āĻŋāĻ CPU āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻāĻāĻŋ āĻĻā§āĻ°ā§āĻ¤ āĻāĻ˛ā§ āĻāĻŦāĻ PostgreSQL āĻ¸āĻāĻ¸ā§āĻāĻ°āĻŖ 9.6 āĻĨā§āĻā§ āĻāĻāĻŋ āĻāĻ°āĻ¤ā§ āĻ¸āĻā§āĻˇāĻŽ āĻšāĻ¯āĻŧā§āĻā§āĨ¤
āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻŦā§āĻļāĻŋāĻˇā§āĻā§āĻ¯āĻāĻŋ āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨ āĻāĻ°āĻ¤ā§ 3 āĻŦāĻāĻ° āĻ˛ā§āĻā§āĻā§ - āĻāĻŽāĻžāĻĻā§āĻ° āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻ¸āĻŽā§āĻĒāĻžāĻĻāĻ¨ā§āĻ° āĻŦāĻŋāĻāĻŋāĻ¨ā§āĻ¨ āĻĒāĻ°ā§āĻ¯āĻžāĻ¯āĻŧā§ āĻā§āĻĄāĻāĻŋ āĻĒā§āĻ¨āĻ°āĻžāĻ¯āĻŧ āĻ˛āĻŋāĻāĻ¤ā§ āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛āĨ¤ PostgreSQL 9.6 āĻā§āĻĄāĻā§ āĻāĻ°āĻ āĻāĻ¨ā§āĻ¨āĻ¤ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ āĻŦāĻāĻžāĻ āĻžāĻŽā§ āĻāĻžāĻ˛ā§ āĻāĻ°ā§āĻā§āĨ¤ āĻĒāĻ°āĻŦāĻ°ā§āĻ¤ā§ āĻ¸āĻāĻ¸ā§āĻāĻ°āĻŖāĻā§āĻ˛āĻŋāĻ¤ā§, āĻ āĻ¨ā§āĻ¯āĻžāĻ¨ā§āĻ¯ āĻ§āĻ°āĻŖā§āĻ° āĻĒā§āĻ°āĻļā§āĻ¨āĻā§āĻ˛āĻŋ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛āĻāĻžāĻŦā§ āĻāĻžāĻ°ā§āĻ¯āĻāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧāĨ¤
āĻ¸ā§āĻŽāĻžāĻŦāĻĻā§āĻ§āĻ¤āĻž
- āĻ¸āĻŽāĻ¸ā§āĻ¤ āĻā§āĻ° āĻāĻ¤āĻŋāĻŽāĻ§ā§āĻ¯ā§āĻ āĻŦā§āĻ¯āĻ¸ā§āĻ¤ āĻĨāĻžāĻāĻ˛ā§ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻ¸āĻŽā§āĻĒāĻžāĻĻāĻ¨ āĻ¸āĻā§āĻˇāĻŽ āĻāĻ°āĻŦā§āĻ¨ āĻ¨āĻž, āĻ āĻ¨ā§āĻ¯āĻĨāĻžāĻ¯āĻŧ āĻ āĻ¨ā§āĻ¯āĻžāĻ¨ā§āĻ¯ āĻ āĻ¨ā§āĻ°ā§āĻ§āĻā§āĻ˛āĻŋ āĻ§ā§āĻ° āĻšāĻ¯āĻŧā§ āĻ¯āĻžāĻŦā§āĨ¤
- āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻā§āĻ°ā§āĻ¤ā§āĻŦāĻĒā§āĻ°ā§āĻŖ, āĻāĻā§āĻ WORK_MEM āĻŽāĻžāĻ¨ āĻ¸āĻš āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖ āĻ āĻ¨ā§āĻ āĻŽā§āĻŽāĻ°āĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ - āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻšā§āĻ¯āĻžāĻļ āĻ¯ā§āĻāĻĻāĻžāĻ¨ āĻŦāĻž āĻ¸āĻžāĻāĻžāĻ¨ā§āĻ° āĻāĻžāĻ_āĻŽā§āĻŽ āĻŽā§āĻŽāĻ°āĻŋ āĻā§āĻ°āĻšāĻŖ āĻāĻ°ā§āĨ¤
- āĻāĻŽ āĻ˛ā§āĻā§āĻ¨ā§āĻ¸āĻŋ OLTP āĻĒā§āĻ°āĻļā§āĻ¨ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻ¸āĻŽā§āĻĒāĻžāĻĻāĻ¨ āĻĻā§āĻŦāĻžāĻ°āĻž āĻ¤ā§āĻŦāĻ°āĻžāĻ¨ā§āĻŦāĻŋāĻ¤ āĻāĻ°āĻž āĻ¯āĻžāĻŦā§ āĻ¨āĻžāĨ¤ āĻāĻŦāĻ āĻ¯āĻĻāĻŋ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻāĻāĻāĻŋ āĻ¸āĻžāĻ°āĻŋ āĻĒā§āĻ°āĻĻāĻžāĻ¨ āĻāĻ°ā§, āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖ āĻļā§āĻ§ā§āĻŽāĻžāĻ¤ā§āĻ° āĻāĻāĻŋāĻā§ āĻ§ā§āĻ° āĻāĻ°ā§ āĻĻā§āĻŦā§āĨ¤
- āĻŦāĻŋāĻāĻžāĻļāĻāĻžāĻ°ā§āĻ°āĻž TPC-H āĻŦā§āĻā§āĻāĻŽāĻžāĻ°ā§āĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻ¤ā§ āĻĒāĻāĻ¨ā§āĻĻ āĻāĻ°ā§āĨ¤ āĻšāĻ¯āĻŧāĻ¤ā§ āĻāĻĒāĻ¨āĻžāĻ° āĻ¨āĻŋāĻā§āĻāĻ¤ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻŽā§āĻ¤ā§āĻ¯ā§āĻĻāĻ¨ā§āĻĄā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ āĻ¨ā§āĻ°ā§āĻĒ āĻĒā§āĻ°āĻļā§āĻ¨ āĻāĻā§āĨ¤
- āĻĒā§āĻ°ā§āĻŦāĻ¨āĻŋāĻ°ā§āĻ§āĻžāĻ°āĻŋāĻ¤ āĻ˛āĻāĻŋāĻ āĻāĻžāĻĄāĻŧāĻž āĻļā§āĻ§ā§āĻŽāĻžāĻ¤ā§āĻ° SELECT āĻĒā§āĻ°āĻļā§āĻ¨āĻā§āĻ˛āĻŋ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛āĻāĻžāĻŦā§ āĻāĻžāĻ°ā§āĻ¯āĻāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧāĨ¤
- āĻāĻāĻ¨āĻ āĻāĻāĻ¨āĻ āĻ¸āĻ āĻŋāĻ āĻ¸ā§āĻā§āĻāĻ°āĻŖ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻŽā§āĻĄā§ āĻā§āĻ°āĻŽāĻŋāĻ āĻā§āĻŦāĻŋāĻ˛ āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨āĻŋāĻāĻ¯āĻŧā§āĻ° āĻā§āĻ¯āĻŧā§ āĻāĻžāĻ˛āĨ¤
- āĻĒāĻ āĻāĻ°āĻž āĻĒā§āĻ°āĻļā§āĻ¨ āĻāĻŦāĻ āĻāĻžāĻ°ā§āĻ¸āĻžāĻ° āĻ¸āĻŽāĻ°ā§āĻĨāĻŋāĻ¤ āĻ¨āĻ¯āĻŧāĨ¤
- āĻāĻāĻ¨ā§āĻĄā§ āĻĢāĻžāĻāĻļāĻ¨ āĻāĻŦāĻ āĻ āĻ°ā§āĻĄāĻžāĻ°āĻā§āĻ¤ āĻ¸ā§āĻ āĻ¸āĻžāĻŽāĻā§āĻ°āĻŋāĻ āĻĢāĻžāĻāĻļāĻ¨ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻ¨āĻ¯āĻŧāĨ¤
- I/O āĻāĻžāĻā§āĻ° āĻāĻžāĻĒā§ āĻāĻĒāĻ¨āĻŋ āĻāĻŋāĻā§āĻ āĻ˛āĻžāĻ āĻāĻ°āĻŦā§āĻ¨ āĻ¨āĻžāĨ¤
- āĻā§āĻ¨ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻŦāĻžāĻāĻžāĻ āĻ ā§āĻ¯āĻžāĻ˛āĻāĻ°āĻŋāĻĻāĻŽ āĻāĻā§. āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻāĻŋāĻā§ āĻŦāĻŋāĻˇāĻ¯āĻŧā§ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛āĻāĻžāĻŦā§ āĻŦāĻŋāĻāĻŋāĻ¨ā§āĻ¨ āĻ§āĻ°āĻŖā§āĻ° āĻĒā§āĻ°āĻļā§āĻ¨ āĻāĻ°āĻž āĻ¯ā§āĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤
- āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖ āĻ¸āĻā§āĻˇāĻŽ āĻāĻ°āĻ¤ā§ āĻāĻāĻāĻŋ āĻ¨ā§āĻ¸ā§āĻā§āĻĄ SELECT āĻĻāĻŋāĻ¯āĻŧā§ CTE (āĻ¸āĻš ...) āĻĒā§āĻ°āĻ¤āĻŋāĻ¸ā§āĻĨāĻžāĻĒāĻ¨ āĻāĻ°ā§āĻ¨āĨ¤
- āĻ¤ā§āĻ¤ā§āĻ¯āĻŧ āĻĒāĻā§āĻˇā§āĻ° āĻĄā§āĻāĻž āĻŽā§āĻĄāĻŧāĻāĻā§āĻ˛āĻŋ āĻāĻāĻ¨āĻ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖ āĻ¸āĻŽāĻ°ā§āĻĨāĻ¨ āĻāĻ°ā§ āĻ¨āĻž (āĻ¤āĻŦā§ āĻ¤āĻžāĻ°āĻž āĻĒāĻžāĻ°ā§!)
- āĻ¸āĻŽā§āĻĒā§āĻ°ā§āĻŖ āĻŦāĻžāĻāĻ°ā§āĻ° āĻ¯ā§āĻāĻĻāĻžāĻ¨ āĻ¸āĻŽāĻ°ā§āĻĨāĻŋāĻ¤ āĻ¨āĻ¯āĻŧāĨ¤
- max_rows āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖ āĻ¨āĻŋāĻˇā§āĻā§āĻ°āĻŋāĻ¯āĻŧ āĻāĻ°ā§āĨ¤
- āĻ¯āĻĻāĻŋ āĻāĻāĻāĻŋ āĻĒā§āĻ°āĻļā§āĻ¨ā§ āĻāĻŽāĻ¨ āĻāĻāĻāĻŋ āĻĢāĻžāĻāĻļāĻ¨ āĻĨāĻžāĻā§ āĻ¯āĻž āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻ¨āĻŋāĻ°āĻžāĻĒāĻĻ āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻāĻŋāĻšā§āĻ¨āĻŋāĻ¤ āĻāĻ°āĻž āĻšāĻ¯āĻŧ āĻ¨āĻž, āĻ¤āĻŦā§ āĻāĻāĻŋ āĻāĻāĻ āĻĨā§āĻ°ā§āĻĄā§āĻĄ āĻšāĻŦā§ā§ˇ
- āĻ¸āĻŋāĻ°āĻŋāĻ¯āĻŧāĻžāĻ˛āĻžāĻāĻā§āĻŦāĻ˛ āĻ˛ā§āĻ¨āĻĻā§āĻ¨ āĻŦāĻŋāĻā§āĻāĻŋāĻ¨ā§āĻ¨āĻ¤āĻž āĻ¸ā§āĻ¤āĻ° āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖ āĻ āĻā§āĻˇāĻŽ āĻāĻ°ā§āĨ¤
āĻĒāĻ°ā§āĻā§āĻˇāĻžāĻ° āĻĒāĻ°āĻŋāĻŦā§āĻļ
PostgreSQL āĻŦāĻŋāĻāĻžāĻļāĻāĻžāĻ°ā§āĻ°āĻž TPC-H āĻŦā§āĻā§āĻāĻŽāĻžāĻ°ā§āĻ āĻĒā§āĻ°āĻļā§āĻ¨ā§āĻ° āĻĒā§āĻ°āĻ¤āĻŋāĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻ¸āĻŽāĻ¯āĻŧ āĻāĻŽāĻžāĻ¨ā§āĻ° āĻā§āĻˇā§āĻāĻž āĻāĻ°ā§āĻā§āĨ¤ āĻŦā§āĻā§āĻāĻŽāĻžāĻ°ā§āĻ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°ā§āĻ¨ āĻāĻŦāĻ
- āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°ā§āĻ¨ TPC-H_Tools_v2.17.3.zip (āĻŦāĻž āĻ¨āĻ¤ā§āĻ¨ āĻ¸āĻāĻ¸ā§āĻāĻ°āĻŖ)
TPC āĻ āĻĢāĻ¸āĻžāĻāĻ āĻĨā§āĻā§ . - makefile.suite āĻāĻ° āĻ¨āĻžāĻŽ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨ āĻāĻ°ā§ Makefile āĻāĻ°ā§āĻ¨ āĻāĻŦāĻ āĻāĻāĻžāĻ¨ā§ āĻŦāĻ°ā§āĻŖāĻŋāĻ¤ āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨ āĻāĻ°ā§āĻ¨:
https://github.com/tvondra/pg_tpch . āĻŽā§āĻ āĻāĻŽāĻžāĻ¨ā§āĻĄ āĻĻāĻŋāĻ¯āĻŧā§ āĻā§āĻĄ āĻāĻŽā§āĻĒāĻžāĻāĻ˛ āĻāĻ°ā§āĻ¨āĨ¤ - āĻĄā§āĻāĻž āĻ¤ā§āĻ°āĻŋ āĻāĻ°ā§āĻ¨:
./dbgen -s 10
āĻāĻāĻāĻŋ 23 āĻāĻŋāĻŦāĻŋ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ āĻ¤ā§āĻ°āĻŋ āĻāĻ°ā§āĨ¤ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻāĻŦāĻ āĻ -āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒā§āĻ°āĻļā§āĻ¨ā§āĻ° āĻāĻ°ā§āĻŽāĻā§āĻˇāĻŽāĻ¤āĻž āĻĒāĻžāĻ°ā§āĻĨāĻā§āĻ¯ āĻĻā§āĻāĻ¤ā§ āĻāĻāĻŋ āĻ¯āĻĨā§āĻˇā§āĻāĨ¤ - āĻĢāĻžāĻāĻ˛ āĻāĻ¨āĻāĻžāĻ°ā§āĻ āĻāĻ°ā§āĻ¨
tbl
вcsv Ņ for
иsed
. - āĻ¸āĻāĻā§āĻ°āĻšāĻ¸ā§āĻĨāĻ˛ āĻā§āĻ˛ā§āĻ¨ āĻāĻ°ā§āĻ¨
pg_tpch
āĻāĻŦāĻ āĻĢāĻžāĻāĻ˛ āĻāĻĒāĻŋ āĻāĻ°ā§āĻ¨csv
вpg_tpch/dss/data
. - āĻāĻāĻāĻŋ āĻāĻŽāĻžāĻ¨ā§āĻĄ āĻĻāĻŋāĻ¯āĻŧā§ āĻĒā§āĻ°āĻļā§āĻ¨ āĻ¤ā§āĻ°āĻŋ āĻāĻ°ā§āĻ¨
qgen
. - āĻāĻŽāĻžāĻ¨ā§āĻĄ āĻĻāĻŋāĻ¯āĻŧā§ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§ āĻĄā§āĻāĻž āĻ˛ā§āĻĄ āĻāĻ°ā§āĻ¨
./tpch.sh
.
āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻā§āĻ°āĻŽāĻŋāĻ āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨āĻŋāĻ
āĻāĻāĻŋ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒāĻĄāĻŧāĻžāĻ° āĻāĻžāĻ°āĻŖā§ āĻĻā§āĻ°ā§āĻ¤āĻ¤āĻ° āĻšāĻ¤ā§ āĻĒāĻžāĻ°ā§, āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻĄā§āĻāĻž āĻ āĻ¨ā§āĻ CPU āĻā§āĻ°ā§ āĻāĻĄāĻŧāĻŋāĻ¯āĻŧā§ āĻĨāĻžāĻāĻžāĻ° āĻāĻžāĻ°āĻŖā§āĨ¤ āĻāĻ§ā§āĻ¨āĻŋāĻ āĻ āĻĒāĻžāĻ°ā§āĻāĻŋāĻ āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽā§, 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
āĻā§āĻ°āĻŽāĻŋāĻ āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨āĻāĻŋ āĻāĻāĻ¤ā§āĻ°ā§āĻāĻ°āĻŖ āĻāĻžāĻĄāĻŧāĻžāĻ āĻ āĻ¨ā§āĻāĻā§āĻ˛āĻŋ āĻ¸āĻžāĻ°āĻŋ āĻ¤ā§āĻ°āĻŋ āĻāĻ°ā§, āĻ¤āĻžāĻ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§āĻāĻŋ āĻāĻāĻāĻŋ āĻāĻāĻ CPU āĻā§āĻ° āĻĻā§āĻŦāĻžāĻ°āĻž āĻāĻžāĻ°ā§āĻ¯āĻāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧāĨ¤
āĻ¯āĻĻāĻŋ āĻāĻĒāĻ¨āĻŋ āĻ¯ā§āĻ āĻāĻ°ā§āĻ¨ 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
āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻ¸āĻŽāĻˇā§āĻāĻŋ
āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ Seq āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨ āĻ¨ā§āĻĄ āĻāĻāĻļāĻŋāĻ āĻāĻāĻ¤ā§āĻ°āĻŋāĻ¤āĻāĻ°āĻŖā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ¸āĻžāĻ°āĻŋ āĻ¤ā§āĻ°āĻŋ āĻāĻ°ā§āĨ¤ "āĻāĻāĻļāĻŋāĻ āĻ¸āĻŽāĻˇā§āĻāĻŋ" āĻ¨ā§āĻĄ āĻāĻ āĻ˛āĻžāĻāĻ¨āĻā§āĻ˛āĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻāĻžāĻāĻāĻžāĻ āĻāĻ°ā§ 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) āĻŦāĻžāĻ°āĨ¤
āĻāĻāĻž āĻāĻŋāĻāĻžāĻŦā§ āĻāĻžāĻ āĻāĻ°ā§?
āĻĒā§āĻ°āĻ¸ā§āĻ¸
āĻ
āĻ¨ā§āĻ°ā§āĻ§ āĻ¸āĻŽā§āĻĒāĻžāĻĻāĻ¨ āĻ¸āĻ°ā§āĻŦāĻĻāĻž āĻ
āĻā§āĻ°āĻŖā§ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻĻāĻŋāĻ¯āĻŧā§ āĻļā§āĻ°ā§ āĻšāĻ¯āĻŧāĨ¤ āĻ¨ā§āĻ¤āĻž āĻ¸āĻŦāĻāĻŋāĻā§ āĻ
-āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻāĻŦāĻ āĻāĻŋāĻā§ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖ āĻāĻ°ā§āĨ¤ āĻāĻāĻ āĻ
āĻ¨ā§āĻ°ā§āĻ§āĻā§āĻ˛āĻŋ āĻ¸āĻŽā§āĻĒāĻžāĻĻāĻ¨ āĻāĻ°ā§ āĻāĻŽāĻ¨ āĻ
āĻ¨ā§āĻ¯āĻžāĻ¨ā§āĻ¯ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻā§āĻ˛āĻŋāĻā§ āĻāĻ°ā§āĻŽā§ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻŦāĻ˛āĻž āĻšāĻ¯āĻŧāĨ¤ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖ āĻ
āĻŦāĻāĻžāĻ āĻžāĻŽā§ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§
āĻŽāĻŋāĻĨāĻˇā§āĻā§āĻ°āĻŋāĻ¯āĻŧāĻž
āĻāĻ°ā§āĻŽā§ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻā§āĻ˛āĻŋ āĻāĻāĻāĻŋ āĻŦāĻžāĻ°ā§āĻ¤āĻž āĻ¸āĻžāĻ°āĻŋāĻ° āĻŽāĻžāĻ§ā§āĻ¯āĻŽā§ āĻ¨ā§āĻ¤āĻžāĻ° āĻ¸āĻžāĻĨā§ āĻ¯ā§āĻāĻžāĻ¯ā§āĻ āĻāĻ°ā§ (āĻļā§āĻ¯āĻŧāĻžāĻ° āĻāĻ°āĻž āĻŽā§āĻŽāĻ°āĻŋāĻ° āĻāĻĒāĻ° āĻāĻŋāĻ¤ā§āĻ¤āĻŋ āĻāĻ°ā§)āĨ¤ āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻ° 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)
āĻ¸āĻāĻā§āĻ°āĻš āĻļā§āĻˇ āĻĒāĻ°ā§āĻ¯āĻžāĻ¯āĻŧā§ āĻāĻā§, āĻ¤āĻžāĻ āĻ¨ā§āĻ¸ā§āĻā§āĻĄ āĻ˛ā§āĻĒ āĻŦāĻžāĻŽ āĻ¯ā§āĻ āĻāĻāĻāĻŋ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻ
āĻĒāĻžāĻ°ā§āĻļāĻ¨āĨ¤ āĻĒā§āĻ¯āĻžāĻ°āĻžāĻ˛āĻžāĻ˛ āĻāĻ¨āĻĄā§āĻā§āĻ¸ āĻ
āĻ¨āĻ˛āĻŋ āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨ āĻļā§āĻ§ā§āĻŽāĻžāĻ¤ā§āĻ° 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
TPC-H āĻĨā§āĻā§ āĻĒā§āĻ°āĻļā§āĻ¨ 12 āĻ¸ā§āĻĒāĻˇā§āĻāĻāĻžāĻŦā§ āĻāĻāĻāĻŋ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛ āĻšā§āĻ¯āĻžāĻļ āĻ¸āĻāĻ¯ā§āĻ āĻĻā§āĻāĻžāĻ¯āĻŧāĨ¤ āĻĒā§āĻ°āĻ¤āĻŋāĻāĻŋ āĻāĻ°ā§āĻŽā§ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻāĻāĻāĻŋ āĻ¸āĻžāĻ§āĻžāĻ°āĻŖ āĻšā§āĻ¯āĻžāĻļ āĻā§āĻŦāĻŋāĻ˛ āĻ¤ā§āĻ°āĻŋāĻ¤ā§ āĻ āĻŦāĻĻāĻžāĻ¨ āĻ°āĻžāĻā§āĨ¤
āĻ¯ā§āĻāĻĻāĻžāĻ¨ āĻāĻāĻ¤ā§āĻ°āĻŋāĻ¤ āĻāĻ°ā§āĻ¨
āĻāĻāĻāĻŋ āĻŽāĻžāĻ°ā§āĻ āĻ¯ā§āĻ āĻĒā§āĻ°āĻā§āĻ¤āĻŋāĻ¤ā§ āĻ -āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛āĨ¤ āĻāĻŋāĻ¨ā§āĻ¤āĻž āĻāĻ°āĻŦā§āĻ¨ āĻ¨āĻž āĻ¯āĻĻāĻŋ āĻāĻāĻŋ āĻĒā§āĻ°āĻļā§āĻ¨ā§āĻ° āĻļā§āĻˇ āĻ§āĻžāĻĒ āĻšāĻ¯āĻŧ - āĻāĻāĻŋ āĻāĻāĻ¨āĻ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛āĻāĻžāĻŦā§ āĻāĻ˛āĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤
-- 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-āĻ
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
â āĻ¸āĻžāĻ°ā§āĻāĻžāĻ°ā§ CPU āĻā§āĻ°ā§āĻ° āĻ¸āĻāĻā§āĻ¯āĻžāĻ° āĻ¸āĻžāĻĨā§ āĻāĻ°ā§āĻŽā§ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻ° āĻŽā§āĻ āĻ¸āĻāĻā§āĻ¯āĻž āĻ¸āĻžāĻŽāĻā§āĻāĻ¸ā§āĻ¯ āĻāĻ°ā§āĨ¤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 11-āĻ āĻ¸āĻŽāĻžāĻ¨ā§āĻ¤āĻ°āĻžāĻ˛āĻ¤āĻž
āĻāĻ¤ā§āĻ¸: www.habr.com