|
11.07.2014, 10:21:27
#38694054
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Откуда: Космос
Сообщения: 563
Рейтинг:
0
/ 0
|
|
|
|
В принципе по скорости меня устраивает. Таблицы эти будут в дальнейшем наполняться минимально. Может что то не очень хорошо сделал? На первый взгляд, какие могу встретить грабли?
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
EXPLAIN ANALYZE SELECT
n.id AS n_id,
cd.id AS cd_id,
cd.label AS cd_label,
n.min_w AS n_min_w,
n.max_w AS n_max_w,
csr.w AS csr_rw,
n.min_h AS n_min_h,
n.max_h AS n_max_h,
csr.h AS csr_rh,
n.min_c AS n_min_c,
n.max_c AS n_max_c,
csr.c AS csr_rc,
n.w AS n_dw,
n.h AS n_dh,
n.c AS n_dc,
n.def_str AS n_str,
n.syntax AS n_flag,
n.visible_client AS n_vs,
n.use_legs AS n_ul,
ntd.vis AS ntd_vis,
ntd.indent_w AS ntd_iw,
ntd.indent_h AS ntd_ih,
ntd.indent_c AS ntd_ic,
cdc_calc_a.label AS cdc_A,
cdc_calc_depend_a.label AS cdc_depend_A,
ntd.calc_a AS ntd_calc_a,
cdc_calc_b.label AS cdc_B,
cdc_calc_depend_b.label AS cdc_depend_B,
ntd.calc_b AS ntd_calc_b,
cbd.label AS cbd_label,
ntd.const_b AS ntd_cb,
cdc_cond_whc.label AS cdc_whc_label,
cdc_sign_whc.label AS cdc_whc_sign,
ntd.cacl_whc_size AS ntd_whc_size,
cdc_action_whc.label AS cdc_whc_action,
cd_whc.label AS cd_whc_detail,
ntd.cacl_whc_count AS ntd_whc_count,
cdc_plane_whc.label AS cdc_whc_plane,
cdc_sign_c.label AS cdc_calc_c_sign,
ntd.cacl_c_count_use AS ntd_calc_c_count_use,
cdc_action_c.label AS cdc_calc_c_action,
ntd.cacl_c_count AS ntd_calc_c_count,
cd_calc_c.label AS cd_calc_c_detail,
cdk.label AS cdk_label,
ntd.kit_count AS ntd_kc,
ckd.label AS ckd_label,
ntd.kit_depend_count AS ntd_kdc,
ntd.kit_depend_size AS ntd_kds
FROM cup_name AS n
JOIN cup_ntd AS ntd ON ntd.id_name=n.id
JOIN cup_detail AS cd ON cd.id=ntd.id_detail
JOIN cup_range AS csr ON csr.id_cupname=n.id
JOIN cup_detail_calc AS cdc_calc_a ON cdc_calc_a.id=ntd.a
JOIN cup_detail_calc AS cdc_calc_b ON cdc_calc_b.id=ntd.b
JOIN cup_detail_calc_depend AS cdc_calc_depend_a ON cdc_calc_depend_a.id=ntd.depend_a
JOIN cup_detail_calc_depend AS cdc_calc_depend_b ON cdc_calc_depend_b.id=ntd.depend_b
JOIN cup_detail_border AS cbd ON cbd.id=ntd.border
JOIN cup_detail_kit AS cdk ON cdk.id=ntd.kit_id
JOIN cup_kit_depend AS ckd ON ckd.id=ntd.kit_depend_type
JOIN cup_detail_calc_condition AS cdc_cond_whc ON cdc_cond_whc.id=ntd.cacl_whc_condition
JOIN cup_detail_calc_sign AS cdc_sign_whc ON cdc_sign_whc.id=ntd.cacl_whc_condition
JOIN cup_detail_calc_action AS cdc_action_whc ON cdc_action_whc.id=ntd.cacl_whc_action
FULL JOIN cup_detail AS cd_whc ON cd_whc.id=ntd.cacl_whc_id_detail
JOIN cup_detail_calc_plane AS cdc_plane_whc ON cdc_plane_whc.id=ntd.cacl_whc_plane
JOIN cup_detail_calc_sign AS cdc_sign_c ON cdc_sign_c.id=ntd.cacl_c_sign
JOIN cup_detail_calc_action AS cdc_action_c ON cdc_action_c.id=ntd.cacl_c_action
FULL JOIN cup_detail AS cd_calc_c ON cd_calc_c.id=ntd.cacl_c_id_detail
WHERE
n.label=:par_lab
ORDER BY ntd_vis DESC, cd_label;
EXPLAIN ANALYZE
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
"Sort (cost=158.32..158.41 rows=34 width=587) (actual time=3.324..3.326 rows=41 loops=1)"
" Sort Key: ntd.vis, cd.label"
" Sort Method: quicksort Memory: 42kB"
" -> Hash Left Join (cost=12.77..157.46 rows=34 width=587) (actual time=0.387..3.043 rows=41 loops=1)"
" Hash Cond: (ntd.cacl_c_id_detail = cd_calc_c.id)"
" -> Nested Loop (cost=10.89..155.11 rows=34 width=570) (actual time=0.348..2.974 rows=41 loops=1)"
" -> Nested Loop (cost=10.74..147.91 rows=34 width=542) (actual time=0.343..2.861 rows=41 loops=1)"
" -> Nested Loop (cost=10.59..140.70 rows=34 width=514) (actual time=0.340..2.749 rows=41 loops=1)"
" -> Hash Left Join (cost=10.44..133.49 rows=34 width=486) (actual time=0.334..2.630 rows=41 loops=1)"
" Hash Cond: (ntd.cacl_whc_id_detail = cd_whc.id)"
" -> Nested Loop (cost=8.56..131.14 rows=34 width=469) (actual time=0.290..2.547 rows=41 loops=1)"
" -> Nested Loop (cost=8.41..123.94 rows=34 width=441) (actual time=0.284..2.436 rows=41 loops=1)"
" -> Nested Loop (cost=8.25..116.73 rows=34 width=421) (actual time=0.280..2.320 rows=41 loops=1)"
" -> Hash Join (cost=8.10..109.52 rows=34 width=381) (actual time=0.273..2.200 rows=41 loops=1)"
" Hash Cond: (ntd.kit_depend_type = ckd.id)"
" -> Nested Loop (cost=6.97..107.92 rows=34 width=372) (actual time=0.240..2.135 rows=41 loops=1)"
" -> Hash Join (cost=6.82..100.79 rows=34 width=344) (actual time=0.233..2.009 rows=41 loops=1)"
" Hash Cond: (ntd.border = cbd.id)"
" -> Nested Loop (cost=5.55..99.06 rows=34 width=344) (actual time=0.198..1.941 rows=41 loops=1)"
" -> Nested Loop (cost=5.39..91.85 rows=34 width=316) (actual time=0.195..1.828 rows=41 loops=1)"
" -> Nested Loop (cost=5.24..84.64 rows=34 width=288) (actual time=0.189..1.711 rows=41 loops=1)"
" -> Nested Loop (cost=5.09..77.43 rows=34 width=260) (actual time=0.186..1.598 rows=41 loops=1)"
" -> Hash Join (cost=4.94..70.23 rows=34 width=232) (actual time=0.172..1.453 rows=41 loops=1)"
" Hash Cond: (ntd.id_detail = cd.id)"
" -> Hash Join (cost=3.06..67.85 rows=42 width=203) (actual time=0.077..1.376 rows=47 loops=1)"
" Hash Cond: (ntd.id_name = n.id)"
" -> Seq Scan on cup_ntd ntd (cost=0.00..58.27 rows=1627 width=128) (actual time=0.005..1.113 rows=1627 loops=1)"
" -> Hash (cost=3.05..3.05 rows=1 width=87) (actual time=0.056..0.056 rows=1 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Hash Join (cost=1.50..3.05 rows=1 width=87) (actual time=0.044..0.055 rows=1 loops=1)"
" Hash Cond: (csr.id_cupname = n.id)"
" -> Seq Scan on cup_range csr (cost=0.00..1.39 rows=39 width=20) (actual time=0.008..0.017 rows=39 loops=1)"
" -> Hash (cost=1.49..1.49 rows=1 width=67) (actual time=0.019..0.019 rows=1 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on cup_name n (cost=0.00..1.49 rows=1 width=67) (actual time=0.017..0.017 rows=1 loops=1)"
" Filter: (label = 'ШНГ'::text)"
" Rows Removed by Filter: 38"
" -> Hash (cost=1.39..1.39 rows=39 width=33) (actual time=0.028..0.028 rows=42 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 3kB"
" -> Seq Scan on cup_detail cd (cost=0.00..1.39 rows=39 width=33) (actual time=0.005..0.011 rows=42 loops=1)"
" -> Index Scan using cup_detail_calc_a_pkey on cup_detail_calc cdc_calc_a (cost=0.15..0.20 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.a)"
" -> Index Scan using cup_detail_calc_a_pkey on cup_detail_calc cdc_calc_b (cost=0.15..0.20 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.b)"
" -> Index Scan using cup_detail_calc_depend_a_pkey on cup_detail_calc_depend cdc_calc_depend_a (cost=0.15..0.20 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.depend_a)"
" -> Index Scan using cup_detail_calc_depend_a_pkey on cup_detail_calc_depend cdc_calc_depend_b (cost=0.15..0.20 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.depend_b)"
" -> Hash (cost=1.12..1.12 rows=12 width=12) (actual time=0.015..0.015 rows=12 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on cup_detail_border cbd (cost=0.00..1.12 rows=12 width=12) (actual time=0.003..0.005 rows=12 loops=1)"
" -> Index Scan using cup_detail_kit_pkey on cup_detail_kit cdk (cost=0.15..0.20 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.kit_id)"
" -> Hash (cost=1.06..1.06 rows=6 width=21) (actual time=0.016..0.016 rows=6 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on cup_kit_depend ckd (cost=0.00..1.06 rows=6 width=21) (actual time=0.008..0.009 rows=6 loops=1)"
" -> Index Scan using cup_detail_calc_sign_pkey on cup_detail_calc_sign cdc_sign_whc (cost=0.15..0.20 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.cacl_whc_condition)"
" -> Index Scan using cup_detail_calc_condition_pkey on cup_detail_calc_condition cdc_cond_whc (cost=0.15..0.20 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.cacl_whc_condition)"
" -> Index Scan using cup_detail_calc_action_pkey on cup_detail_calc_action cdc_action_whc (cost=0.15..0.20 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.cacl_whc_action)"
" -> Hash (cost=1.39..1.39 rows=39 width=33) (actual time=0.033..0.033 rows=42 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 3kB"
" -> Seq Scan on cup_detail cd_whc (cost=0.00..1.39 rows=39 width=33) (actual time=0.004..0.014 rows=42 loops=1)"
" -> Index Scan using cup_detail_calc_plane_pkey on cup_detail_calc_plane cdc_plane_whc (cost=0.15..0.20 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.cacl_whc_plane)"
" -> Index Scan using cup_detail_calc_sign_pkey on cup_detail_calc_sign cdc_sign_c (cost=0.15..0.20 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.cacl_c_sign)"
" -> Index Scan using cup_detail_calc_action_pkey on cup_detail_calc_action cdc_action_c (cost=0.15..0.20 rows=1 width=40) (actual time=0.001..0.002 rows=1 loops=41)"
" Index Cond: (id = ntd.cacl_c_action)"
" -> Hash (cost=1.39..1.39 rows=39 width=33) (actual time=0.023..0.023 rows=42 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 3kB"
" -> Seq Scan on cup_detail cd_calc_c (cost=0.00..1.39 rows=39 width=33) (actual time=0.003..0.010 rows=42 loops=1)"
"Total runtime: 4.194 ms"
|
|
|
