αααα½ααα»ααΆααααα’αααα’αα·ααααααααΆααααα DBA ααααααΆααα¬ααα αΆααα’αΆααΈαααααααΆααααα α±ααα’αααααααΉααααΆ 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"
ααα»ααααααΆαα’αΆααααααΆααα αααα»αα’ααααα "ααΈαααααΉα" ααΊαα·ααΆααα·ααα·αα αααΆααααΆααα
- ααααΌαααΆααααα αΆααα
αααα»α node ααααΌααααααααΆααααααΆααα
αααααΊααΎααααΈααααααααααΆααΎααΆααααΌαα αααΆααααααα»ααααΆαααΎααααΈααααα·ααααα·ααααΆααααΆααααΆαααα½α α¬ααΆααΎααΆαα’αΆααααααΈααΆααΆαααΆααααα·ααααααααΈααΈαααα»ααααΆααααΆααα·αααααΆαα α’αααααααΌααααα½αα ααααΈαα½ααααα - ααααΌαααΆααααααααΆαααα node αα»αααΉαααααα·ααα»α
ααΆα ααΆααααα·ααααααΆααααα·ααααα·ααΆααααα»αααααΆααααα»ααααααααΌαααααΎ "αα αααα»αααααΆα" - αααααΆααααΈααΆααα’αα αααααααΆααααα·ααααα·ααααΌαααΆαα ααα’α»ααααα αΆαααΆααΆααααααααααΆααααΆαααααα·ααααα·ααααααΆαααα½α α αΎαααΆα’αΆα ααΆαααΆαααα - ααΆααΆααααααΎαααΆαα α αΎαααΆααα’ααααααα½αααααΆααΆααΆααααΎαααΈααΆαααααΎααααα½αα αααα - ααΌα ααααα’αααααΆ "αααααααΆααααααααααα»α"?
αα ααααααααΎαααααΆααΆαααααααααΆααα’ααααααα ααΆααα’αααα’αα·ααααααααΆα αααΎαααααΆααααααααΎα ααΎαααΆαααΉαααΆααΈααΆααααα ααΆααΎααα ααΌα αααα
α αΎααααααΆααααααΆααΎαααααΌαααΆα ...
α§ααααα
αα
αααα»αααα ααΎαααΆαααααΆααΆααααααΌαααααΆαα·αααααΆαααααΆααα’αα ααααα½αα±ααααα "α’αααααΆααααΌαααααΈαααααα αα·αα’αααΈαααααααΌαααααΎ" ααααααΆααααααΆα αα·αααααΎα ααΆααΆααααααΎαααΆαα α αΎαα
αααααααααααααα½ααααααα·αααααααααα’αααααΆαα½ααα ααααα
αα½ααα·αααααΎ -
ααΆαααΎαααΎααααααααΆα
ααΎβααΆβααΆαβαααα½αβαααα»αβααΆαβαααβααΈβαααβααΆαβαα βαααβαααβααΆβααΎαβαα βααΌα βαααβαα?
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
αα·ααααααα
ααα»ααααααΌα ααα, αααα»αααααααα’ααααααΆαααα ααααααααΌα ααΆααααααΆαααααααΌαααΆααααααααΆααΆααααα αααΆααα
ααα»ααααααααα·αααΎαααααΆαααΆαααααααα»αααααΆα ααΆααααΉααααα½ααααααααα ααΆαα
ααα
αΆααααααααΆ piechart αααααααΆααα
ααΆααΆααααααΎαααΆαα αααααΆαααααααΎαααααααΆααααα»α ααΆαααααααΆαααα½α ααααΌαααΆααααααααΆα:
ααΆα§ααΆα ααα ααΆαααααΆαααΆααα·αααααΆαααα
αααααααααααΆααα½αα’αΆα
ααΆαα«αααααα·αααααΆααα
αααΎαααΆααα½αα
ααααα»ααα ααΆαααααααα
ααΆααΆααααααΎαααΆαα ααααα·αααΎαα ααΆααααααααααΆααααΌααααααααΆα αα·αα ααα»α ααΊα αΆααααααααΆααααΌαααΆαααΆααα αα αα·αα’αΆα ααΎαααΎααα½α α αΎα α ααα»α’αααΈαα·αααΌααααααΆαααα½αααΆαα α’αααα’αα·αααααα α αΎααααααααα½αααΆααΆ "ααΆααΆαα»ααααΈ"?
ααΎαβααΆαβαααααΌαβααααΌβα’αα»ααΆαααβαα½αβα ααα½αβαα½α βα αΎαβα
αααααααααααΆααααα½αααΆααααααΆαα
α₯α‘αΌαααα ααααα·αααΎα’αααααΆαααααα αΌααααα½αααΎααα αααα»ααααααΆααααααΆααα·ααΆα α’αααα’αΆα ααΎαααΆααΎααααΌαα αααΆααααααααΆααα»ααααΆααααααΆααααα ααααΈαααααααΆαααααΈαα½αα - α’αααΈαα½αααΌα αααα
... α¬ααΌααααΈααααΌα
αααα
ααΆααααα½ααααΆαααΆαααααααα αααα»αααααΎ
ααααα·αααΎα’ααα "ααααΆαα" αα·αααααΉαααααααΎαα ααααααααα»ααααααα ααα»ααααααααΆααααΆαααΆααααααααααααΆααΈαααααΆαααααα’α·ααααααααα ααα»ααααα α’αααα’αΆα α ααααααΆαα αααα»ααααααΎααα½ααααα»αα αααααααααΎαα
- ααΆαα½αααΉαααΆααααα½αααααααα
αααα»ααααα½α
αααααΆααααΆαααααα·ααααα·αααααααΆαααα ααΎααΌαααααΆαααααα’ααα αα·αααααααααααααSELECT 'const', 'param'::text;
- ααΆαα½αααΉαααΆααααα½ααααααααΆαααα PREPARE/EXECUTE
ααΎααααΈααααΆααααΆαααΆαααΆαααααα’ααααααααααααα ααααααααααααααΆαααΆααααααα’αΆα ααααΌαααΆααα·αα’αΎααΎ - α§ααΆα ααααα αααααααΎααΆαααΎααΆααΆααααα ααDEALLOCATE ALL; PREPARE q(text) AS SELECT 'const', $1::text; EXECUTE q('param'::text);
αααααααΆααααααααΆα
αα·αααααΆαα, αα·ααΆα, α
αααααααααΆαα½ααα ααΆααΈ! ααααααβααΉαβαα
βααβαααααΆβαα»αβαααα»αβαααααααΆα α αΎαβα’αααβα’αΆα
βαααα‘ααβαα
βααΆβαα·αβαα
βαααβαααααα
ααα»ααααααααα·αααΎα’ααααα·αα ααα±ααα’ααααααααΎαααααααααααα’ααααα αα»αααααα αα·αα·αααααΎααααα’αα "αα»αααααααααααΆααααα»ααααααααΆα"α
αα
αααα»αα’αααααααΆαααααααααα»αααΉααα·ααΆαα’αααΈααΆαααααΆα αα·αααΆααααααα
α
α·ααααααααΎαα‘αΎααα
ααααα·ααΆααααααΆαα
ααααα: www.habr.com