ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / разные планы выполнения на боевой и развернутой тестовой бд. причины?
10 сообщений из 10, страница 1 из 1
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031364
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
пока нашел только одно отличие:
в самой бд:
на боевой базе:
LC_COLLATE = 'ru_RU.UTF-8'
LC_CTYPE = 'ru_RU.UTF-8'
на тестовой
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'

все остальное совпадает. физически на разных машинах, оперативы намного больше на боевом.

в запросе какрас идет сравнение построчное
Код: sql
1.
2.
3.
4.
5.
6.
7.
...
from Wperson0 n0
	inner join Wperson1 n1 on 
		n0.firstname = n1.firstname  
		and n0.middlename=n1.middlename
		and lower(n0.secondname)=lower(n1.secondname) 
...


на боевом значения берутся по индексу
на тестовом идет прямое обращение к бд=(

может быть причина разных планов в LC_COLLATE, LC_CTYPE или еще искать причины?
...
Рейтинг: 0 / 0
18.08.2015, 10:22
| Ответить | Цитировать | Написать  
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031373
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
АГДЕСАМИПЛАНЫЕПТ
...
Рейтинг: 0 / 0
18.08.2015, 10:33
| Ответить | Цитировать | Написать  
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031406
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
Ivan Durak, пример (все лишнее поубирал)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with 	Wperson0 as ( -- ФИО взятое из документа (в одном доке участвует два участника)
		with Wdoc as (select distinct unnest(ARRAY[80404130,80404134]) as person_id )
		select distinct np.firstname, np.middlename, np.secondname, np.birth_date from Wdoc doc inner join ibd.person np on np.person_id = doc.person_id),
	Wperson1 as ( -- все совпадения ФИО + дата, дата может быть не указана
		select distinct np.person_id
		from Wperson0 np0 
		inner join ibd.person np on 
			np0.firstname = np.firstname and 
			np0.middlename=np.middlename and 
			lower(np0.secondname)=lower(np.secondname) and 
			coalesce(np0.birth_date,np.birth_date,'1000-10-10')=coalesce(np.birth_date,np0.birth_date,'1000-10-10'))
	select * from Wperson1


ЕПТСАМПЛАННАТЕСТОВОЙ:
 

Код: sql
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.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
"CTE Scan on wperson1  (cost=42567559.04..42567563.04 rows=200 width=8)"
"  CTE wperson0"
"    ->  Unique  (cost=10388098.51..10951726.81 rows=9018053 width=54)"
"          CTE wdoc"
"            ->  HashAggregate  (cost=0.76..1.26 rows=100 width=0)"
"                  Group Key: unnest('{80404130,80404134}'::integer[])"
"                  ->  Result  (cost=0.00..0.51 rows=100 width=0)"
"          ->  Sort  (cost=10388097.24..10500822.90 rows=45090264 width=54)"
"                Sort Key: np.firstname, np.middlename, np.secondname, np.birth_date"
"                ->  Nested Loop  (cost=0.00..32184.00 rows=45090264 width=54)"
"                      ->  CTE Scan on wdoc doc  (cost=0.00..2.00 rows=100 width=4)"
"                      ->  Append  (cost=0.00..321.43 rows=39 width=63)"
"                            ->  Seq Scan on person np  (cost=0.00..1.01 rows=1 width=108)"
"                                  Filter: (doc.person_id = person_id)"
"                            ->  Index Scan using person_a_pkey on person_a np_1  (cost=0.43..8.45 rows=1 width=62)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_ani_pkey on person_ani np_2  (cost=0.43..8.45 rows=1 width=64)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_b_pkey on person_b np_3  (cost=0.43..8.45 rows=1 width=62)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_before_a_pkey on person_before_a np_4  (cost=0.42..8.08 rows=1 width=25)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_bj_pkey on person_bj np_5  (cost=0.43..8.45 rows=1 width=61)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_ch_pkey on person_ch np_6  (cost=0.43..8.41 rows=1 width=61)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_d_pkey on person_d np_7  (cost=0.43..8.45 rows=1 width=62)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_e_pkey on person_e np_8  (cost=0.43..8.45 rows=1 width=60)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_f_pkey on person_f np_9  (cost=0.43..8.45 rows=1 width=64)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_ga_pkey on person_ga np_10  (cost=0.43..8.41 rows=1 width=66)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_gak_pkey on person_gak np_11  (cost=0.43..8.41 rows=1 width=63)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_gam_pkey on person_gam np_12  (cost=0.43..8.45 rows=1 width=63)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_gb_pkey on person_gb np_13  (cost=0.43..8.45 rows=1 width=64)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_h_pkey on person_h np_14  (cost=0.43..8.45 rows=1 width=65)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_han_pkey on person_han np_15  (cost=0.43..8.45 rows=1 width=63)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_i_pkey on person_i np_16  (cost=0.43..8.45 rows=1 width=61)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_ka_pkey on person_ka np_17  (cost=0.43..8.45 rows=1 width=62)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_kb_pkey on person_kb np_18  (cost=0.43..8.45 rows=1 width=62)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_kot_pkey on person_kot np_19  (cost=0.43..8.45 rows=1 width=62)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_ll_pkey on person_ll np_20  (cost=0.43..8.45 rows=1 width=60)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_ma_pkey on person_ma np_21  (cost=0.43..8.45 rows=1 width=61)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_mb_pkey on person_mb np_22  (cost=0.43..8.45 rows=1 width=64)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_mk_pkey on person_mk np_23  (cost=0.43..8.45 rows=1 width=65)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_n_pkey on person_n np_24  (cost=0.43..8.45 rows=1 width=64)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_o_pkey on person_o np_25  (cost=0.43..8.45 rows=1 width=60)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_pj_pkey on person_pj np_26  (cost=0.43..8.41 rows=1 width=62)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_r_pkey on person_r np_27  (cost=0.43..8.45 rows=1 width=66)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_sa_pkey on person_sa np_28  (cost=0.43..8.45 rows=1 width=63)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_sal_pkey on person_sal np_29  (cost=0.43..8.45 rows=1 width=63)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_sb_pkey on person_sb np_30  (cost=0.43..8.45 rows=1 width=62)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_sche_pkey on person_sche np_31  (cost=0.42..8.37 rows=1 width=59)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_sh_pkey on person_sh np_32  (cost=0.43..8.45 rows=1 width=65)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_shao_pkey on person_shao np_33  (cost=0.43..8.45 rows=1 width=63)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_sp_pkey on person_sp np_34  (cost=0.43..8.45 rows=1 width=64)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_t_pkey on person_t np_35  (cost=0.43..8.45 rows=1 width=61)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_v_pkey on person_v np_36  (cost=0.43..8.45 rows=1 width=61)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_ya_pkey on person_ya np_37  (cost=0.43..8.41 rows=1 width=60)"
"                                  Index Cond: (person_id = doc.person_id)"
"                            ->  Index Scan using person_z_pkey on person_z np_38  (cost=0.43..8.45 rows=1 width=63)"
"                                  Index Cond: (person_id = doc.person_id)"
"  CTE wperson1"
"    ->  HashAggregate  (cost=31615830.24..31615832.24 rows=200 width=8)"
"          Group Key: np_39.person_id"
"          ->  Merge Join  (cost=28458280.94..31614559.53 rows=508283 width=8)"
"                Merge Cond: (((np0.firstname)::text = (np_39.firstname)::text) AND ((np0.middlename)::text = (np_39.middlename)::text) AND ((lower((np0.secondname)::text)) = (lower((np_39.secondname)::text))))"
"                Join Filter: (COALESCE(np0.birth_date, np_39.birth_date, '1000-10-10'::date) = COALESCE(np_39.birth_date, np0.birth_date, '1000-10-10'::date))"
"                ->  Sort  (cost=2208499.88..2231045.01 rows=9018053 width=100)"
"                      Sort Key: np0.firstname, np0.middlename, (lower((np0.secondname)::text))"
"                      ->  CTE Scan on wperson0 np0  (cost=0.00..180361.06 rows=9018053 width=100)"
"                ->  Materialize  (cost=26249781.06..26700683.70 rows=90180529 width=63)"
"                      ->  Sort  (cost=26249781.06..26475232.38 rows=90180529 width=63)"
"                            Sort Key: np_39.firstname, np_39.middlename, (lower((np_39.secondname)::text))"
"                            ->  Result  (cost=0.00..4162348.29 rows=90180529 width=63)"
"                                  ->  Append  (cost=0.00..4162348.29 rows=90180529 width=63)"
"                                        ->  Seq Scan on person np_39  (cost=0.00..1.01 rows=1 width=108)"
"                                        ->  Seq Scan on person_a np_40  (cost=0.00..128304.80 rows=2774380 width=62)"
"                                        ->  Seq Scan on person_ani np_41  (cost=0.00..131198.13 rows=2883713 width=64)"
"                                        ->  Seq Scan on person_b np_42  (cost=0.00..107618.99 rows=2339799 width=62)"
"                                        ->  Seq Scan on person_before_a np_43  (cost=0.00..6276.55 rows=194755 width=25)"
"                                        ->  Seq Scan on person_bj np_44  (cost=0.00..95143.78 rows=2113978 width=61)"
"                                        ->  Seq Scan on person_ch np_45  (cost=0.00..50579.77 rows=1106477 width=61)"
"                                        ->  Seq Scan on person_d np_46  (cost=0.00..89875.80 rows=1891680 width=62)"
"                                        ->  Seq Scan on person_e np_47  (cost=0.00..91370.60 rows=1970260 width=60)"
"                                        ->  Seq Scan on person_f np_48  (cost=0.00..124432.03 rows=2700703 width=64)"
"                                        ->  Seq Scan on person_ga np_49  (cost=0.00..79674.47 rows=1707447 width=66)"
"                                        ->  Seq Scan on person_gak np_50  (cost=0.00..70813.38 rows=1552738 width=63)"
"                                        ->  Seq Scan on person_gam np_51  (cost=0.00..87713.80 rows=1903780 width=63)"
"                                        ->  Seq Scan on person_gb np_52  (cost=0.00..171576.74 rows=3729574 width=64)"
"                                        ->  Seq Scan on person_h np_53  (cost=0.00..124026.57 rows=2651557 width=65)"
"                                        ->  Seq Scan on person_han np_54  (cost=0.00..125328.80 rows=2701480 width=63)"
"                                        ->  Seq Scan on person_i np_55  (cost=0.00..135630.39 rows=2880139 width=61)"
"                                        ->  Seq Scan on person_ka np_56  (cost=0.00..127814.01 rows=2680101 width=62)"
"                                        ->  Seq Scan on person_kb np_57  (cost=0.00..129525.71 rows=2755471 width=62)"
"                                        ->  Seq Scan on person_kot np_58  (cost=0.00..119535.51 rows=2563451 width=62)"
"                                        ->  Seq Scan on person_ll np_59  (cost=0.00..91826.92 rows=1958392 width=60)"
"                                        ->  Seq Scan on person_ma np_60  (cost=0.00..123427.77 rows=2617077 width=61)"
"                                        ->  Seq Scan on person_mb np_61  (cost=0.00..121491.38 rows=2549838 width=64)"
"                                        ->  Seq Scan on person_mk np_62  (cost=0.00..137537.27 rows=2889427 width=65)"
"                                        ->  Seq Scan on person_n np_63  (cost=0.00..167442.77 rows=3614177 width=64)"
"                                        ->  Seq Scan on person_o np_64  (cost=0.00..98850.92 rows=2184492 width=60)"
"                                        ->  Seq Scan on person_pj np_65  (cost=0.00..78172.47 rows=1722547 width=62)"
"                                        ->  Seq Scan on person_r np_66  (cost=0.00..141720.37 rows=3221237 width=66)"
"                                        ->  Seq Scan on person_sa np_67  (cost=0.00..86264.40 rows=1873740 width=63)"
"                                        ->  Seq Scan on person_sal np_68  (cost=0.00..109139.92 rows=2344792 width=63)"
"                                        ->  Seq Scan on person_sb np_69  (cost=0.00..133134.63 rows=2923363 width=62)"
"                                        ->  Seq Scan on person_sche np_70  (cost=0.00..31609.92 rows=687392 width=59)"
"                                        ->  Seq Scan on person_sh np_71  (cost=0.00..97603.25 rows=2083125 width=65)"
"                                        ->  Seq Scan on person_shao np_72  (cost=0.00..105774.90 rows=2290090 width=63)"
"                                        ->  Seq Scan on person_sp np_73  (cost=0.00..131460.08 rows=3051708 width=64)"
"                                        ->  Seq Scan on person_t np_74  (cost=0.00..146056.83 rows=3185683 width=61)"
"                                        ->  Seq Scan on person_v np_75  (cost=0.00..135189.84 rows=2953284 width=61)"
"                                        ->  Seq Scan on person_ya np_76  (cost=0.00..59949.12 rows=1376412 width=60)"
"                                        ->  Seq Scan on person_z np_77  (cost=0.00..169254.69 rows=3552269 width=63)"


ЕПТСАМПЛАННАБОЕВОМ:
 
Код: sql
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.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
"CTE Scan on wperson1  (cost=747.95..747.97 rows=1 width=8)"
"  CTE wperson0"
"    ->  HashAggregate  (cost=342.88..342.89 rows=1 width=54)"
"          CTE wdoc"
"            ->  HashAggregate  (cost=0.02..0.03 rows=1 width=0)"
"                  ->  Result  (cost=0.00..0.01 rows=1 width=0)"
"          ->  Nested Loop  (cost=0.00..342.84 rows=1 width=54)"
"                Join Filter: (doc.person_id = np.person_id)"
"                ->  CTE Scan on wdoc doc  (cost=0.00..0.02 rows=1 width=4)"
"                ->  Append  (cost=0.00..342.33 rows=39 width=63)"
"                      ->  Index Scan using person_pkey on person np  (cost=0.00..8.27 rows=1 width=108)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_before_a_pkey on person_before_a np  (cost=0.00..8.33 rows=1 width=25)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_v_pkey on person_v np  (cost=0.00..9.00 rows=1 width=61)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_gb_pkey on person_gb np  (cost=0.00..9.33 rows=1 width=64)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_d_pkey on person_d np  (cost=0.00..8.81 rows=1 width=62)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_e_pkey on person_e np  (cost=0.00..8.84 rows=1 width=60)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_z_pkey on person_z np  (cost=0.00..9.31 rows=1 width=63)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_i_pkey on person_i np  (cost=0.00..9.10 rows=1 width=61)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_ka_pkey on person_ka np  (cost=0.00..9.04 rows=1 width=62)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_ll_pkey on person_ll np  (cost=0.00..8.84 rows=1 width=61)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_ma_pkey on person_ma np  (cost=0.00..9.02 rows=1 width=61)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_n_pkey on person_n np  (cost=0.00..9.30 rows=1 width=64)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_r_pkey on person_r np  (cost=0.00..9.08 rows=1 width=66)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_t_pkey on person_t np  (cost=0.00..9.17 rows=1 width=61)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_f_pkey on person_f np  (cost=0.00..9.04 rows=1 width=64)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_ch_pkey on person_ch np  (cost=0.00..8.58 rows=1 width=61)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_sche_pkey on person_sche np  (cost=0.00..8.42 rows=1 width=59)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_ya_pkey on person_ya np  (cost=0.00..8.63 rows=1 width=60)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_ga_pkey on person_ga np  (cost=0.00..8.70 rows=1 width=66)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_gak_pkey on person_gak np  (cost=0.00..8.44 rows=1 width=63)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_gam_pkey on person_gam np  (cost=0.00..8.48 rows=1 width=63)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_ani_pkey on person_ani np  (cost=0.00..8.58 rows=1 width=64)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_a_pkey on person_a np  (cost=0.00..8.89 rows=1 width=62)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_sb_pkey on person_sb np  (cost=0.00..9.10 rows=1 width=62)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_sp_pkey on person_sp np  (cost=0.00..8.60 rows=1 width=64)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_h_pkey on person_h np  (cost=0.00..8.87 rows=1 width=65)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_han_pkey on person_han np  (cost=0.00..8.56 rows=1 width=63)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_kb_pkey on person_kb np  (cost=0.00..8.94 rows=1 width=62)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_kot_pkey on person_kot np  (cost=0.00..8.55 rows=1 width=62)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_mb_pkey on person_mb np  (cost=0.00..8.85 rows=1 width=64)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_mk_pkey on person_mk np  (cost=0.00..8.58 rows=1 width=65)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_b_pkey on person_b np  (cost=0.00..8.84 rows=1 width=62)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_bj_pkey on person_bj np  (cost=0.00..8.50 rows=1 width=62)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_sa_pkey on person_sa np  (cost=0.00..8.70 rows=1 width=63)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_sal_pkey on person_sal np  (cost=0.00..8.52 rows=1 width=63)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_sh_pkey on person_sh np  (cost=0.00..8.74 rows=1 width=65)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_shao_pkey on person_shao np  (cost=0.00..8.52 rows=1 width=63)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_o_pkey on person_o np  (cost=0.00..8.77 rows=1 width=60)"
"                            Index Cond: (person_id = doc.person_id)"
"                      ->  Index Scan using person_pj_pkey on person_pj np  (cost=0.00..8.46 rows=1 width=62)"
"                            Index Cond: (person_id = doc.person_id)"
"  CTE wperson1"
"    ->  HashAggregate  (cost=405.05..405.06 rows=1 width=8)"
"          ->  Nested Loop  (cost=0.00..405.05 rows=1 width=8)"
"                Join Filter: (((np0.firstname)::text = (np.firstname)::text) AND ((np0.middlename)::text = (np.middlename)::text) AND (COALESCE(np0.birth_date, np.birth_date, '1000-10-10'::date) = COALESCE(np.birth_date, np0.birth_date, '1000-10-10'::date)) AND (lower((np0.secondname)::text) = lower((np.secondname)::text)))"
"                ->  CTE Scan on wperson0 np0  (cost=0.00..0.02 rows=1 width=100)"
"                ->  Append  (cost=0.00..404.05 rows=39 width=63)"
"                      ->  Index Scan using person_ix1 on person np  (cost=0.00..8.27 rows=1 width=108)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_before_a_ix1 on person_before_a np  (cost=0.00..8.44 rows=1 width=25)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_v_ix1 on person_v np  (cost=0.00..11.75 rows=1 width=61)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_gb_ix1 on person_gb np  (cost=0.00..12.54 rows=1 width=64)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_d_ix1 on person_d np  (cost=0.00..10.74 rows=1 width=62)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_e_ix1 on person_e np  (cost=0.00..10.92 rows=1 width=60)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_z_ix1 on person_z np  (cost=0.00..12.73 rows=1 width=63)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_i_ix1 on person_i np  (cost=0.00..11.89 rows=1 width=61)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_ka_ix1 on person_ka np  (cost=0.00..11.64 rows=1 width=62)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_ll_ix1 on person_ll np  (cost=0.00..10.88 rows=1 width=61)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_ma_ix1 on person_ma np  (cost=0.00..11.64 rows=1 width=61)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_n_ix1 on person_n np  (cost=0.00..12.74 rows=1 width=64)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_r_ix1 on person_r np  (cost=0.00..11.91 rows=1 width=66)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_t_ix1 on person_t np  (cost=0.00..12.20 rows=1 width=61)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_f_ix1 on person_f np  (cost=0.00..11.52 rows=1 width=64)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_ch_ix1 on person_ch np  (cost=0.00..9.68 rows=1 width=61)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_sche_ix1 on person_sche np  (cost=0.00..9.13 rows=1 width=59)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_ya_ix1 on person_ya np  (cost=0.00..10.01 rows=1 width=60)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_ga_ix1 on person_ga np  (cost=0.00..10.21 rows=1 width=66)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_gak_ix1 on person_gak np  (cost=0.00..8.72 rows=1 width=63)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_gam_ix1 on person_gam np  (cost=0.00..8.83 rows=1 width=63)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_ani_ix1 on person_ani np  (cost=0.00..9.12 rows=1 width=64)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_a_ix1 on person_a np  (cost=0.00..11.62 rows=1 width=62)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_sb_ix1 on person_sb np  (cost=0.00..11.60 rows=1 width=62)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_sp_ix1 on person_sp np  (cost=0.00..9.23 rows=1 width=64)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_h_ix1 on person_h np  (cost=0.00..10.13 rows=1 width=65)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_han_ix1 on person_han np  (cost=0.00..9.06 rows=1 width=63)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_kb_ix1 on person_kb np  (cost=0.00..11.40 rows=1 width=62)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_kot_ix1 on person_kot np  (cost=0.00..9.03 rows=1 width=62)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_mb_ix1 on person_mb np  (cost=0.00..11.22 rows=1 width=64)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_mk_ix1 on person_mk np  (cost=0.00..9.12 rows=1 width=65)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_b_ix1 on person_b np  (cost=0.00..11.12 rows=1 width=62)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_bj_ix1 on person_bj np  (cost=0.00..8.90 rows=1 width=62)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_sa_ix1 on person_sa np  (cost=0.00..9.61 rows=1 width=63)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_sal_ix1 on person_sal np  (cost=0.00..8.96 rows=1 width=63)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_sh_ix1 on person_sh np  (cost=0.00..9.72 rows=1 width=65)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_shao_ix1 on person_shao np  (cost=0.00..8.95 rows=1 width=63)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_o_ix1 on person_o np  (cost=0.00..10.06 rows=1 width=60)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"
"                      ->  Index Scan using person_pj_ix1 on person_pj np  (cost=0.00..8.78 rows=1 width=62)"
"                            Index Cond: (((firstname)::text = (np0.firstname)::text) AND ((middlename)::text = (np0.middlename)::text))"

...
Рейтинг: 0 / 0
18.08.2015, 11:09
| Ответить | Цитировать | Написать  
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031487
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Legushka,

попробуйте явно указать планировщику сколько строк в wdoc:

так:
Код: sql
1.
with wdoc as (values (80404130),(80404134))


либо так (+стоит использовать distinct on (pk/unique field) если это возможно, чтобы не сортировать результат):
Код: sql
1.
2.
with 	Wperson0 as ( -- ФИО взятое из документа (в одном доке участвует два участника)
		select distinct np.firstname, np.middlename, np.secondname, np.birth_date from Wdoc doc where doc.person_id = any(array[80404130,80404134]),
...
Рейтинг: 0 / 0
18.08.2015, 12:20
| Ответить | Цитировать | Написать  
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031611
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
ну дык на тесте он решил что пардон затратно делать 9 мильенов индекс сиков.
Проще один скан и сорт.
Вот статистика на тесте по wperson0:
CTE Scan on wperson0 np0 (cost=0.00..180361.06 rows=9018053 width=100)"


А вот на бою:
CTE Scan on wperson0 np0 (cost=0.00..0.02 rows=1 width=100)"

Либо такое разное количество записей, либо статистика недостоверная
...
Рейтинг: 0 / 0
18.08.2015, 14:07
| Ответить | Цитировать | Написать  
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031678
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
Ivan, один запрос выполняется за 240-2000ms
на тестовом бесконечно долго
каждая партиция содержит 1-4млн записей

на боевом и на тестовом количество примерно одинаковое

неужели оперативка определяет как выполнять запрос?
...
Рейтинг: 0 / 0
18.08.2015, 14:54
| Ответить | Цитировать | Написать  
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031699
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
LegushkaIvan, один запрос выполняется за 240-2000ms
на тестовом бесконечно долго
каждая партиция содержит 1-4млн записей

на боевом и на тестовом количество примерно одинаковое

неужели оперативка определяет как выполнять запрос?

Наиболее вероятные варианты:
1)разные настройки базы на боевом и тестовом
2)на тестовом почему то нет нужных индексов
3)разные major версии базы на боевом и тестовом

Учитывая что вывод explain несколько отличается в части

автор" CTE wdoc"
" -> HashAggregate (cost=0.76..1.26 rows=100 width=0)"
" Group Key: unnest('{80404130,80404134}'::integer[])"
" -> Result (cost=0.00..0.51 rows=100 width=0)"


vs
автор" CTE wdoc"
" -> HashAggregate (cost=0.02..0.03 rows=1 width=0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"


я склоняюсь в варианту 3 (и/или похаченому ROWS у функции unnest на боевом сервере).

Приведите вывод
Код: plaintext
explain analyze select * from  unnest(ARRAY[80404130,80404134]);
на тестовой и боевой базе.



--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
18.08.2015, 15:06
| Ответить | Цитировать | Написать  
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031758
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
Maxim Bogukя склоняюсь в варианту 3 (и/или похаченому ROWS у функции unnest на боевом сервере).
вы правы:
Т: "PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit"
Б: "PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit"
--
Maxim BogukПриведите вывод
explain analyze select * from unnest(ARRAY[80404130,80404134]);
на тестовой и боевой базе.

Т:
 
"Function Scan on unnest (cost=0.00..1.00 rows=100 width=4) (actual time=0.097..0.100 rows=2 loops=1)"
"Planning time: 0.145 ms"
"Execution time: 0.161 ms"

Б:
 
"Function Scan on unnest (cost=0.00..1.00 rows=100 width=4) (actual time=0.032..0.033 rows=2 loops=1)"
"Total runtime: 0.068 ms"

получается что на очень старом постгресе БД работает быстрее?
...
Рейтинг: 0 / 0
18.08.2015, 16:20
| Ответить | Цитировать | Написать  
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031762
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
Maxim Boguk , спасибо, избавилася от этой функции и на тестовой стало работать по индексам
...
Рейтинг: 0 / 0
18.08.2015, 16:24
| Ответить | Цитировать | Написать  
разные планы выполнения на боевой и развернутой тестовой бд. причины?
    #39031780
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
на тесте, т.к. 9.4. можно пофантазировать сюда:
 
Код: sql
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.
with
Wperson0 as ( -- ФИО взятое из документа (в одном доке участвует два участника)
	with Wdoc as (SELECT * FROM (select distinct unnest(ARRAY[80404130,80404134]) as person_id ) foo LIMIT 2 -- всегда не больше 2 ? -- так и скажите планёру
		)
	--select distinct np.firstname, np.middlename, np.secondname, np.birth_date from Wdoc doc inner join ibd.person np on np.person_id = doc.person_id
	SELECT DISTINCT --только если оба дока -- одна морда лица
		np.firstname, np.middlename, np.secondname, np.birth_date from Wdoc doc
	FROM Wdoc
	,LATERAL (SELECT np.firstname, np.middlename, np.secondname, np.birth_date from ibd.person np WHERE np.person_id = doc.person_id 
		ORDER BY np.person_id LIMIT 1) np -- навязываем однократный seek по каждому person_id из 2-х
	),

Wperson1 as ( -- все совпадения ФИО + дата, дата может быть не указана
	select /*distinct*/ np.person_id -- сдаётся -- это pk
	from Wperson0 np0 
	inner join
	FROM ibd.person np on 
		np0.firstname = np.firstname and 
		np0.middlename=np.middlename and 
		lower(np0.secondname)=lower(np.secondname) and 
		--coalesce(np0.birth_date,np.birth_date,'1000-10-10')=coalesce(np.birth_date,np0.birth_date,'1000-10-10')) -- за такое -- убивать
		AND ((np0.birth_date = np.birth_date) OR (np0.birth_date IS NULL AND np.birth_date IS NULL))
		-- какие тут есть [составные/ф-ые]индексы ? -- вот тут можно немного покопаться
	)
select * from Wperson1



и вообще -- любовь к дистинктам на мильонных табличках -- это что-то.

в бою, к сожалению, "лейтерал" не скажешь
...
Рейтинг: 0 / 0
18.08.2015, 16:40
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / разные планы выполнения на боевой и развернутой тестовой бд. причины?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39031406&tid=1997813]:
 0ms
get settings:
 7ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 152ms
get topic data:
 6ms
get forum data:
 2ms
get page messages:
 27ms
get tp. blocked users:
 1ms
others: 227ms
total:  440ms
созд. / загр.: 440ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]