|
Временные файлы
|
|||
---|---|---|---|
#18+
Почему создаются временные файлы, если вызвать функцию, которая выполняет запрос. Если выполнить сам запрос, то временные файлы не создаются. Например, вызываем функцию: Код: sql 1.
План запроса: Function Scan on func (cost=2.50..7.50 rows=500 width=217) (actual time=58.164..58.165 rows=15 loops=1) Buffers: shared hit=4508, temp read=92 written=420 Total runtime: 58.180 ms В логе видим ряд подобных записей: Код: sql 1.
Теперь выполним запрос, который имеется в теле функции. Сам запрос приводить не буду, т.к. он достаточно большой. План запроса: QUERY PLAN Merge Left Join (cost=27713.59..24318700.36 rows=4598331 width=217) (actual time=22.220..22.308 rows=15 loops=1) Merge Cond: ((array_remove(t4.nom_path, t4.id_nom)) = pt4.nom_path) Buffers: shared hit=1985 CTE t -> Hash Left Join (cost=1134.80..1136.77 rows=31 width=59) (actual time=18.169..18.199 rows=37 loops=1) Hash Cond: (ds.id_st_g = st.id) Buffers: shared hit=690 -> Hash Left Join (cost=1133.71..1135.22 rows=31 width=103) (actual time=18.162..18.184 rows=37 loops=1) Hash Cond: (t_2.id = ds.id) Buffers: shared hit=689 CTE mo -> Append (cost=0.26..6.52 rows=1 width=4) (actual time=13.147..13.151 rows=8 loops=1) Buffers: shared hit=192 -> Function Scan on get_mgroup_tree (cost=0.26..6.51 rows=1 width=4) (actual time=13.147..13.151 rows=8 loops=1) Filter: (r_is_actual AND (r_type_op = 'н'::bpchar)) Buffers: shared hit=192 CTE vvt -> Recursive Union (cost=0.00..647.72 rows=7396 width=20) (actual time=0.005..2.371 rows=7385 loops=1) Buffers: shared hit=176 -> Append (cost=0.00..573.56 rows=7386 width=20) (actual time=0.005..1.521 rows=7385 loops=1) Buffers: shared hit=176 -> Result (cost=0.00..249.85 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=1) One-Time Filter: NULL::boolean -> Seq Scan on t116_6_reg (cost=0.00..249.85 rows=1 width=20) (never executed) -> Seq Scan on t116_6_reg_vvt t116_6_reg_1 (cost=0.00..249.85 rows=7385 width=20) (actual time=0.003..1.107 rows=7385 loops=1) Buffers: shared hit=176 -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1) One-Time Filter: false CTE t -> Nested Loop Left Join (cost=0.59..201.76 rows=31 width=76) (actual time=13.216..17.698 rows=37 loops=1) Buffers: shared hit=561 -> Nested Loop (cost=0.31..192.20 rows=31 width=52) (actual time=13.213..17.642 rows=37 loops=1) Buffers: shared hit=480 -> Hash Join (cost=0.03..176.06 rows=37 width=16) (actual time=13.203..17.549 rows=37 loops=1) Hash Cond: (vvt.id_morg = mo.id) Buffers: shared hit=368 -> CTE Scan on vvt (cost=0.00..147.92 rows=7396 width=20) (actual time=0.007..3.726 rows=7385 loops=1) Buffers: shared hit=176 -> Hash (cost=0.02..0.02 rows=1 width=4) (actual time=13.157..13.157 rows=8 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB Buffers: shared hit=192 -> CTE Scan on mo (cost=0.00..0.02 rows=1 width=4) (actual time=13.148..13.154 rows=8 loops=1) Buffers: shared hit=192 -> Index Scan using t116_6_reg_pkey on t116_6_reg vvt2 (cost=0.28..0.43 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=37) Index Cond: (id = vvt.id) Filter: ((date_reg <= COALESCE(now(), now())) AND ((date_dep IS NULL) OR (date_dep > COALESCE(now(), now())))) Buffers: shared hit=112 -> Index Scan using t116_6_dis_pkey on t116_6_dis dis (cost=0.27..0.30 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=37) Index Cond: (id_reg_vvt = vvt2.id) Buffers: shared hit=81 CTE bg -> Result (cost=0.29..214.32 rows=31 width=10) (actual time=0.008..4.590 rows=32 loops=1) One-Time Filter: ((now() <= now()) OR (now() IS NULL)) Buffers: shared hit=471 -> Nested Loop (cost=0.29..214.32 rows=31 width=10) (actual time=0.007..4.583 rows=32 loops=1) Buffers: shared hit=471 -> CTE Scan on t (cost=0.00..0.62 rows=31 width=8) (actual time=0.000..4.503 rows=37 loops=1) Buffers: shared hit=363 -> Index Scan using t116_6_bg_new_new_pkey1 on t116_6_bg b (cost=0.28..6.88 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=37) Index Cond: (id_reg_vvt = t.id) Buffers: shared hit=108 CTE ds -> Hash Join (cost=1.01..61.77 rows=19 width=16) (actual time=0.064..0.310 rows=23 loops=1) Hash Cond: (b_1.id_reg_vvt = t_1.id) Buffers: shared hit=20 -> Seq Scan on t116_6_ds b_1 (cost=0.00..56.88 rows=986 width=16) (actual time=0.004..0.221 rows=981 loops=1) Filter: (((dt_end IS NULL) OR (now() <= dt_end)) AND (dt_begin <= COALESCE(now(), now()))) Rows Removed by Filter: 653 Buffers: shared hit=20 -> Hash (cost=0.62..0.62 rows=31 width=8) (actual time=0.011..0.011 rows=37 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB -> CTE Scan on t t_1 (cost=0.00..0.62 rows=31 width=8) (actual time=0.001..0.007 rows=37 loops=1) -> Hash Left Join (cost=1.01..2.05 rows=31 width=94) (actual time=17.833..17.849 rows=37 loops=1) Hash Cond: (t_2.id = bg.id) Buffers: shared hit=669 -> CTE Scan on t t_2 (cost=0.00..0.62 rows=31 width=92) (actual time=13.217..13.223 rows=37 loops=1) Buffers: shared hit=198 -> Hash (cost=0.62..0.62 rows=31 width=10) (actual time=4.610..4.610 rows=32 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB Buffers: shared hit=471 -> CTE Scan on bg (cost=0.00..0.62 rows=31 width=10) (actual time=0.009..4.604 rows=32 loops=1) Buffers: shared hit=471 -> Hash (cost=0.38..0.38 rows=19 width=17) (actual time=0.322..0.322 rows=23 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB Buffers: shared hit=20 -> CTE Scan on ds (cost=0.00..0.38 rows=19 width=17) (actual time=0.065..0.317 rows=23 loops=1) Buffers: shared hit=20 -> Hash (cost=1.04..1.04 rows=4 width=40) (actual time=0.004..0.004 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB Buffers: shared hit=1 -> Seq Scan on enum_st_g st (cost=0.00..1.04 rows=4 width=40) (actual time=0.002..0.003 rows=4 loops=1) Buffers: shared hit=1 CTE t2 -> Recursive Union (cost=0.70..59.86 rows=326 width=99) (actual time=18.229..18.286 rows=37 loops=1) Buffers: shared hit=690 -> CTE Scan on t t_4 (cost=0.70..1.40 rows=16 width=59) (actual time=18.228..18.234 rows=8 loops=1) Filter: ((pid IS NULL) OR (NOT (hashed SubPlan 7))) Rows Removed by Filter: 29 Buffers: shared hit=690 SubPlan 7 -> CTE Scan on t t_3 (cost=0.00..0.62 rows=31 width=8) (actual time=0.001..0.042 rows=37 loops=1) -> Hash Join (cost=1.01..5.20 rows=31 width=99) (actual time=0.007..0.011 rows=10 loops=3) Hash Cond: (t2.id = t_5.pid) -> WorkTable Scan on t2 (cost=0.00..3.20 rows=160 width=48) (actual time=0.000..0.001 rows=12 loops=3) -> Hash (cost=0.62..0.62 rows=31 width=59) (actual time=0.008..0.008 rows=29 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB -> CTE Scan on t t_5 (cost=0.00..0.62 rows=31 width=59) (actual time=0.000..0.004 rows=37 loops=1) CTE t3 -> Append (cost=0.00..2266.23 rows=30326 width=100) (actual time=18.232..19.229 rows=217 loops=1) Buffers: shared hit=1361 -> CTE Scan on t2 t2_1 (cost=0.00..6.52 rows=326 width=100) (actual time=18.231..18.309 rows=37 loops=1) Buffers: shared hit=690 -> Nested Loop (cost=647.84..1959.71 rows=30000 width=80) (actual time=0.430..0.895 rows=180 loops=1) Buffers: shared hit=671 -> Nested Loop (cost=647.83..684.69 rows=30 width=80) (actual time=0.413..0.458 rows=15 loops=1) Buffers: shared hit=671 -> Hash Join (cost=647.55..655.59 rows=30 width=48) (actual time=0.407..0.420 rows=15 loops=1) Hash Cond: (t2_2.id_nom = nom.id) Buffers: shared hit=626 -> CTE Scan on t2 t2_2 (cost=0.00..6.52 rows=326 width=48) (actual time=0.000..0.008 rows=37 loops=1) -> Hash (cost=646.01..646.01 rows=123 width=8) (actual time=0.397..0.397 rows=9 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB Buffers: shared hit=626 -> Hash Join (cost=2.27..646.01 rows=123 width=8) (actual time=0.125..0.396 rows=9 loops=1) Hash Cond: (nom.id_template_use = ptu.id) Buffers: shared hit=626 -> Seq Scan on nomenclature nom (cost=0.00..637.46 rows=1346 width=12) (actual time=0.002..0.243 rows=832 loops=1) Buffers: shared hit=624 -> Hash (cost=2.15..2.15 rows=10 width=4) (actual time=0.097..0.097 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB Buffers: shared hit=2 -> Seq Scan on param_templates_use ptu (cost=0.00..2.15 rows=10 width=4) (actual time=0.056..0.096 rows=3 loops=1) Filter: (((template -> '_data'::text) ->> 'r'::text) IS NOT NULL) Rows Removed by Filter: 2 Buffers: shared hit=2 -> Index Scan using t116_6_param_pkey on t116_6_param p (cost=0.28..0.96 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=15) Index Cond: (id_reg_vvt = t2_2.id) Buffers: shared hit=45 -> Function Scan on generate_series g (cost=0.01..10.01 rows=1000 width=0) (actual time=0.004..0.005 rows=12 loops=15) CTE t4 -> WindowAgg (cost=8761.36..14371.67 rows=30326 width=193) (actual time=22.018..22.157 rows=15 loops=1) Buffers: shared hit=1985 -> GroupAggregate (cost=8761.36..12173.03 rows=30326 width=193) (actual time=22.014..22.140 rows=15 loops=1) Buffers: shared hit=1985 -> Sort (cost=8761.36..8837.17 rows=30326 width=193) (actual time=22.006..22.014 rows=217 loops=1) Sort Key: t3.nom_path, t3.id_nom, t3.pid_nom, t3.is_vvt, nom_1.id, (((nom_1.name_full || CASE WHEN ((nom_1.shifr IS NOT NULL) AND (btrim(nom_1.shifr, ' '::text) <> ''::text)) THEN ((' "'::text || btrim(nom_1.shifr, ' '::text)) || '"'::text) ELSE ''::text END) || CASE WHEN ((nom_1.index IS NOT NULL) AND (btrim(nom_1.index, ' '::text) <> ''::text)) THEN ((' ('::text || btrim(nom_1.index, ' '::text)) || ')'::text) ELSE ''::text END)), (((nom_1.name_short || CASE WHEN ((nom_1.shifr IS NOT NULL) AND (btrim(nom_1.shifr, ' '::text) <> ''::text)) THEN ((' "'::text || btrim(nom_1.shifr, ' '::text)) || '"'::text) ELSE ''::text END) || CASE WHEN ((nom_1.index IS NOT NULL) AND (btrim(nom_1.index, ' '::text) <> ''::text)) THEN ((' ('::text || btrim(nom_1.index, ' '::text)) || ')'::text) ELSE ''::text END)), nom_1.index Sort Method: quicksort Memory: 84kB Buffers: shared hit=1985 -> Hash Join (cost=654.29..3497.35 rows=30326 width=193) (actual time=19.160..20.576 rows=217 loops=1) Hash Cond: (t3.id_nom = nom_1.id) Buffers: shared hit=1985 -> CTE Scan on t3 (cost=0.00..606.52 rows=30326 width=84) (actual time=18.233..19.338 rows=217 loops=1) Buffers: shared hit=1361 -> Hash (cost=637.46..637.46 rows=1346 width=109) (actual time=0.909..0.909 rows=832 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 120kB Buffers: shared hit=624 -> Seq Scan on nomenclature nom_1 (cost=0.00..637.46 rows=1346 width=109) (actual time=0.002..0.696 rows=832 loops=1) Buffers: shared hit=624 -> Sort (cost=6080.53..6156.34 rows=30326 width=209) (actual time=22.192..22.195 rows=15 loops=1) Sort Key: (array_remove(t4.nom_path, t4.id_nom)) Sort Method: quicksort Memory: 32kB Buffers: shared hit=1985 -> CTE Scan on t4 (cost=0.00..606.52 rows=30326 width=209) (actual time=22.023..22.177 rows=15 loops=1) Buffers: shared hit=1985 -> Materialize (cost=3798.53..3950.16 rows=30326 width=40) (actual time=0.011..0.011 rows=13 loops=1) -> Sort (cost=3798.53..3874.34 rows=30326 width=40) (actual time=0.010..0.010 rows=8 loops=1) Sort Key: pt4.nom_path Sort Method: quicksort Memory: 26kB -> CTE Scan on t4 pt4 (cost=0.00..606.52 rows=30326 width=40) (actual time=0.000..0.003 rows=15 loops=1) SubPlan 11 -> Aggregate (cost=5.25..5.26 rows=1 width=64) (actual time=0.006..0.006 rows=1 loops=15) -> HashAggregate (cost=1.50..3.50 rows=100 width=32) (actual time=0.003..0.003 rows=2 loops=15) -> Function Scan on unnest t_6 (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=2 loops=15) Total runtime: 22.803 ms Из плана видно, что временные файлы не используются. Почему выполнение одного и того же по сути запрос, зависит от способа вызова. В одном случае создаются временные файлы, в другом нет. Версия Postgres - 9.3.3 ... |
|||
:
Нравится:
Не нравится:
|
|||
24.10.2017, 08:36 |
|
Временные файлы
|
|||
---|---|---|---|
#18+
Константин Сергеевич Дерерк, Это я понимаю. Но вопрос в другом, почему один и тот же запрос при разных вызовах либо требует дополнительную память, либо нет. ... |
|||
:
Нравится:
Не нравится:
|
|||
24.10.2017, 14:08 |
|
Временные файлы
|
|||
---|---|---|---|
#18+
Константин Сергеевич Дерерк, И как в таких случаях оптимизировать запрос, если не понятно в какой его части не хватает памяти. ... |
|||
:
Нравится:
Не нравится:
|
|||
24.10.2017, 14:13 |
|
Временные файлы
|
|||
---|---|---|---|
#18+
Пересоздал функцию на pl/pgsql, обернув в BEGIN ... END и запрос перестал использовать временные файлы. Ранее думал, что функции на sql использовать эффективней по сравнению с pl/pgsql. А теперь нужно всегда проверять, что эффективней. Печально, что нет объяснения такого поведения. ... |
|||
:
Нравится:
Не нравится:
|
|||
24.10.2017, 17:22 |
|
|
start [/forum/topic.php?fid=53&fpage=64&tid=1996152]: |
0ms |
get settings: |
8ms |
get forum list: |
14ms |
check forum access: |
3ms |
check topic access: |
3ms |
track hit: |
32ms |
get topic data: |
14ms |
get forum data: |
3ms |
get page messages: |
61ms |
get tp. blocked users: |
2ms |
others: | 30ms |
total: | 170ms |
0 / 0 |