ืื ืงืืึทืกืืฉ ืงืฉืื ืึทื ืึท ืืขืืืขืืึธืคึผืขืจ ืืจืขื ืื ืฆื ืืืื ืืืึท ืึธืืขืจ ืึท ืืขืฉืขืคื ืืึทืืืฆืขืจ ืืจืขื ืื ืฆื ืึท PostgreSQL ืงืึธื ืกืืืืึทื ื ืึผืืขื ืฉืืขื ืืืง ืกืึธืื ืืก ืื ืืขืืืข: "ืคืืจืืืืก ื ืขืืขื ืจืืงืืืขืก ืึทืืื ืืึทื ื ืฆื ืคืึทืจืขื ืืืงื ืืืืฃ ืื ืืึทืืึทืืืืก?"
ืืจืึทืืืฆืืึธื ืขื ืืึทื ื ืคืื ืกืืืืช:
- ืืึทืืืึธื ืืฉ ืึทืืืขืจืืืึทื
ืืืขื ืืืจ ืืึทืฉืืืกื ืฆื ืคืึทืจืืื ืื ืขืืืขืืข CTEs ืืืืขืจ ืึท ืคึผืึธืจ ืคืื ืืขื ืก ืคืื ืืืืื ืืขืจ ืคืื ืจืขืงืึธืจืืก - ืึทืืืืืืืื ืกืืึทืืืกืืืง
ืืืื ืื ืคืึทืงืืืฉ ืคืึทืจืฉืคึผืจืืืืื ื ืคืื ืืึทืื ืืื ืื ืืืฉ ืืื ืฉืืื ืืืืขืจ ืึทื ืืขืจืฉ ืคืื ืื ืืขืฆืืข ืืึธื ืืขืืืืื ืืืจื ANALYZE - "ืฆืึทืคึผื" ืืืืฃ ืจืขืกืืจืกื
ืืื ืขืก ืืื ื ืื ืืขืจ ืืขื ืื ืืขืืึทืงืืืืึทื ืงืึทืืคึผืืืืื ื ืืึทืื ืคืื ืื ืงืคึผื, ืืืืืืืื ืคืื ืืึผืจืื ืืขื ืขื ืงืขืกืืืืขืจ ืคึผืึทืืคึผื, ืึธืืขืจ ืืขืจ ืืืกืง ืงืขื ื ืืฉื ืืึทืืื ืึทืจืืืฃ ืืื ืึทืืข ืื "ืืืืื" ืคืื ืื ืืึทืืึทืืืืก - ืืืึทืงืื ื ืคืื ืงืึทืืคึผืืืื ื ืคึผืจืึทืกืขืกืึทื
ืืื ืืืื ืืืึทืงืื ืื ืืขื ืขื ืืึทื ืฅ ืฉืืืขืจ ืฆื ืืึทืคึผื ืืื ืึทื ืึทืืืื, ืืขืืึธืื ืคึฟืึทืจ ืึทืืฅ ืึทื ืืขืจืฉ ืืืจ ืืึทืจืคึฟื ืึธื ืคึฟืจืขื ืคึผืืึทื, ืืืึธืก ืงืขื ืขื ืืืื ืืืงืืืขื ื ืืฆื (ืขืก ืืื ืืขืกืขืจ, ืคืื ืงืืจืก, ืฆื ืืืืื ืืขืจืงืืขืจื (ืึทื ืึทืืืืืจื, ืืึทืคืขืจืก) ...) ืึธืืขืจ .
ืึธืืขืจ, ืืื ืกืืืืืื ืืื ืืขืจ ืืขืืืืงืขืจ ืืึทืงืืืืขื ืืืืฉืึทื,
"ืคึฟืึทืจืฉืืึทื ืขื ืึท ืคึผืืึทื ืืื ืึท ืงืื ืกื, ืืื ืฆื ืืขื ืขืก ืจืืงืืืืืขืจื ืึท ืืืืขืจ ืกืืืข ืคืื โโืืขืจืคืึทืจืื ื ..."
ืึธืืขืจ ืืืจ ืงืขื ืขื ืืึธื ืึธื ืขืก ืืืื ืืืจ ื ืืฆื ืื ืจืขืื ืืขืฆืืึทื!
ืืื ืึทืืื ืงืืงื ืึท ืึธื ืคึฟืจืขื ืคึผืืึทื ืืืคึผืืงืื? ืขืคึผืขืก ืืื ืืึธืก:
Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: (relname = $1)
Filter: (oid = $0)
Buffers: shared hit=4
InitPlan 1 (returns $0,$1)
-> Limit (actual time=0.019..0.020 rows=1 loops=1)
Buffers: shared hit=1
-> Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
Filter: (relkind = 'r'::"char")
Rows Removed by Filter: 5
Buffers: shared hit=1ืึธืืขืจ ืืื ืืึธืก:
"Append (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
" Buffers: shared hit=3"
" CTE cl"
" -> Seq Scan on pg_class (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
" Buffers: shared hit=3"
" -> Limit (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> CTE Scan on cl (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> Limit (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
" Buffers: shared hit=2"
" -> CTE Scan on cl cl_1 (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
" Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"ืึธืืขืจ ืืืืขื ืขื ืืขื ืคึผืืึทื ืืื ืืขืงืกื "ืคืื ืื ืืืึทื" ืืื ืืืืขืจ ืฉืืืขืจ ืืื ืืืงืืึธืจ:
- ืืื ืืขืืืืื ืืื ืื ื ืึธืืข ืกืึทืืึทืงื ืืืจื ืกืืืืจืืข ืจืขืกืืจืกื
ืืึธืก ืืื, ืฆื ืคึฟืึทืจืฉืืืื ืืื ืคืื ืฆืืื ืขืก ืืึธื ืืขื ืืืขื ืฆื ืืืกืคืืจื ืึท ืืึทืืื ืืขืจ ื ืึธืืข, ืึธืืขืจ ืืื ืคืื ืคึผืื ืงื ืืึธืก ืืืืขื ืขื ืคืื ืื ืืืฉ ืืขืืจืืื ืืึทืื ืคืื ืื ืืืกืง, ืืืจ ืืึทืจืคึฟื ืฆื ืขืคืขืก ืึทืจืึธืคึผืจืขืืขื ืขื ืืืื ืขืจ ืคืื ืื ืื ืืขืจืข - ื ืึธืืข ืฆืืื ืืื ืืืจืฃ ืืขืจื ืืืจื ืืืคึผืก
ืืึธ, ืืืกืขืจ ืืื ื ืืฉื ืื ืืขืจืกื ืงืึธืืคึผืืืฆืืจื ืึธืคึผืขืจืึทืฆืืข ืืืึธืก ืืึทืจืฃ ืืืื ืืขืืื "ืืื ืื ืงืึธืคึผ" - ื ืึธื ืึทืืข, ืื ืืืจืืคืืจืื ื ืฆืืื ืืื ืื ืืขืืืืื ืืื ืื ืืืจืืฉื ืืืืขื ืคึฟืึทืจ ืืืื ืืืจืืคืืจืื ื ืคืื ืึท ื ืึธืืข, ืืื ืขืก ืงืขื ืขื ืืืื ืืื ืืขืจืืขืจ ืคืื ืืื - ื ื, ืืื ืืึธืก ืึทืืฅ ืฆืืืึทืืขื ืคึผืจืืืืขื ืฅ ืืื ืื ืฆื ืขื ืืคึฟืขืจื ืื ืืืืคึผื ืงืฉืื - ืึทืืื ืืืขืจ "ืื ืฉืืืึทืืกืืข ืืื ืง"?
ืืืขื ืืืจ ืืขืคืจืืืื ืฆื ืืขืจืงืืขืจื ืึทืืข ืืขื ืฆื ืขืืืขืืข ืืื ืืขืจื ืคืื ืืื ืืืขืจ ืืขืืืขืืึธืคึผืขืจืก, ืืืจ ืืืื ืืขืืขื ืึทื ืคืื ืื ืึทืจืืืก ืขืก ืงืืงื ืขืคึผืขืก ืืื ืืึธืก:
ืืื ืึทื ืืืื ืืืจ ืืึทืจืคึฟื ...
ืงืืืืข
ืืื ืขืก ืืืจ ืืขืคืจืืืื ืฆื ืืึทืืืขื ืึทืืข ืื ืฉืืืกื ืืึทืงืึทื ืืงืก ืืืึธืก ืืขืืคึฟื ืคึฟืึทืจืฉืืืื "ืืืขืจ ืืื ืฆื ืืึทืฉืืืืืงื ืืื ืืืึธืก ืฆื ืืึธื" ืืืื ืืขื ืคึผืืึทื ืืื ืืขืื. ื ื, ืืื ืืืืื ืึท ืืืื ืคืื ืืืื ืืขืจืคืึทืจืื ื ืืื ืื ืงืื.
ืืจืขืคื ืืื ื ืืฆื -
ืืืืืึทืืืืืื ืคืื ืคึผืืึทื ื
ืืื ืขืก ืืจืื ื ืฆื ืคึฟืึทืจืฉืืืื ืืขื ืคึผืืึทื ืืืขื ืขืก ืงืืงื ืืื ืืึธืก?
Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms
ื ืื ืืึทืงืข.
ืืืขืจ ืืืื, ืืื ืึทืืจืืืืืืืืื ืคืึธืจืขืืืืขื ืื ืฉืืืกื ืื ืืืงืึทืืึธืจืก ืืขื ืขื ืืคืืขืฉืืืื, ืขืก ืืื ืคืื ืงืืืจืขืจ:
ืืืขืจ ืืืื ืืขืจ ืคึผืืึทื ืืื ืืขืจ ืงืึธืืคึผืืืฆืืจื, ืขืจ ืืืขื ืงืืืขื ืฆื ืจืึทืืขืืืขื ืคึผืืขืืฉืึทืจื ืฆืืื ืคืึทืจืฉืคึผืจืืืืื ื ืืืจื ื ืึธืืื:
ื ื, ืคึฟืึทืจ ืื ืืขืจืกื ืฉืืืขืจ ืึธืคึผืฆืืขืก ืขืจ ืืื ืืื ืึท ืืึทืื ืฆื ืืขืืคื ืคึผืจืึธืืจืขืก ืืฉืึทืจื:
ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืขืก ืืขื ืขื ืืึทื ืฅ ื ืื-ืืจืืืืืึทื ืกืืืืึทืืืึธื ืก ืืืขื ืึท ืคึผืืึทื ืงืขื ืืึธืื ืืขืจ ืืื ืืืื ืคืึทืงืืืฉ ืืืึธืจืฆื:
ืกืืจืึทืงืืฉืขืจืึทื ืงืืื
ื ื, ืืืื ืื ืืื ืฆืข ืกืืจืืงืืืจ ืคืื ืืขื ืคึผืืึทื ืืื ืืืึทื ืืืืืืืงืืืง ืกืคึผืึทืฅ ืืขื ืขื ืฉืืื ืืขืืืืื ืืืืก ืืื ืงืขื ืืืง, ืืืึธืก ื ืื ืืืืืคึผืื ืงื ืืื ืฆื ืื ืืขืืืขืืึธืคึผืขืจ ืืื ืืขืจืงืืขืจื ืืื ืืื "ืจืืกืืฉ ืฉืคึผืจืึทื"?
ืืืจ ืืึธืื ืฉืืื ืืขืืืืื ืึท ืคึผืึธืจ ืคืื ืืืฅ ืึทืืึท ืจืขืงืึธืืขื ืืึทืฆืืข ืืขืืคึผืืึทืืขืก.
ืฉืืจื-ืืืจื-ืฉืืจื ืึธื ืคึฟืจืขื ืคึผืจืึธืคืืืขืจ
ืืืฆื, ืืืื ืืืจ ืกืืคึผืขืจืึทืืคึผืึธืื ืื ืึธืจืืืื ืขื ืึธื ืคึฟืจืขื ืืืืฃ ืื ืึทื ืึทืืืืื ืคึผืืึทื, ืืืจ ืงืขื ืขื ืืขื ืืื ืคืื ืฆืืื ืืื ืคืืจืืจืืื ืืืืฃ ืืขืืขืจ ืืืื ืืืกืืึธืืื ื - ืขืคึผืขืก ืืื ืืึธืก:
... ืึธืืขืจ ืืคืืื ืืื ืืึธืก:
ืกืึทืืกืืืืืืื ื ืคึผืึทืจืึทืืขืืขืจืก ืืื ืึท ืืงืฉื
ืืืื ืืืจ "ืึทืืึทืืฉื" ื ืื ืืืืื ืึท ืืงืฉื ืฆื ืืขื ืคึผืืึทื, ืึธืืขืจ ืืืื ืื ืคึผืึทืจืึทืืขืืขืจืก ืคืื ืื ืืขืืึทื ืฉืืจื ืคืื ืื ืงืืึธืฅ, ืืืจ ืงืขื ืขื ืืืื ื ืึธืืืึทืื ืขืก ืืื ืืืื ืขืจ ืคืื ืื ืึธืคึผืฆืืขืก:
- ืืื ืืืขืจื ืกืึทืืกืืืืืฉืึทื ืืื ืื ืึธื ืคึฟืจืขื
ืคึฟืึทืจ ืืืจืขืงื ืืืจืืคืืจืื ื ืืืืฃ ืืืื ืืึทืืข ืืื ืืืืึทืืขืจ ืคึผืจืึธืคืืืื ืSELECT 'const', 'param'::text; - ืืื ืืืขืจื ืกืึทืืกืืืืืฉืึทื ืืืจื PREPARE / EXECUTE
ืฆื ืขืืืึทืืืื ืื ืึทืจืืขื ืคืื ืื ืกืงืขืืืฉืืืขืจ, ืืืขื ืื ืคึผืึทืจืึทืืขืืจืืง ืืืื ืงืขื ืขื ืืืื ืืืื ืึธืจืืจื - ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืืืขื ืืจืืขืื ืืืืฃ ืคึผืึทืจืืืฉืึทื ื ืืืฉืDEALLOCATE ALL; PREPARE q(text) AS SELECT 'const', $1::text; EXECUTE q('param'::text);
ืืจืืืื ืคืื ืคืืขื ืขืจ
ืคึผืึทืคึผ, ืคืื ืึทื ืืขืจืงืืืึทืื, ืืืืื ืืื ืืืจืื! ืื ืคึผืืึทื ื ืืืขืื ืืืืืื ืึทืจืืฉืืืืขื ืืื ืืืจ ืงืขื ืขื ืฆืืจืืงืงืืืขื ืฆื ืืื ืฉืคึผืขืืขืจ:
ืึธืืขืจ ืืืื ืืืจ ืืึธื ื ืื ืืืขืื ืึทื ืื ืืขืจืข ืืึธื ืืขื ืืืื ืคึผืืึทื, ืืึธื ื ืื ืคืึทืจืืขืกื ืฆื ืงืึธื ืืจืึธืืืจื ืื ืงืขืกืื "ืืึธื ื ืื ืึทืจืืืกืืขืื ืืื ืึทืจืงืืืื".
ืืื ืื ืคืืืืขื ืืข ืึทืจืืืงืืขื ืืืขื ืืื ืจืขืื ืืืขืื ืื ืฉืืืขืจืืงืืืื ืืื ืืืกืืืฉืึทื ื ืืืึธืก ืฉืืืืขื ืืืขื ืึทื ืึทืืืืืื ื ืึท ืคึผืืึทื.
ืืงืืจ: www.habr.com