ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как сократить время выполнения скрипта?
25 сообщений из 25, страница 1 из 1
Как сократить время выполнения скрипта?
    #38789151
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Код: plsql
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.
EXPLAIN (ANALYZE,BUFFERS)
SELECT c.card_id
FROM  a_v_card_test c 
 
	JOIN template AS avSort_1 ON avSort_1.template_id = c.template_id

	LEFT JOIN a_v_name_test AS avSort_2 
		ON avSort_2.card_id = c.card_id
		AND avSort_2.attribute_code='NAME' 

		 JOIN a_v_name_test av99 ON ( 
					(av99.card_id = c.card_id) 
					AND ( av99.attribute_code IN ('NAME'))
					AND ( av99.string_value IS NOT NULL)
					AND UPPER(av99.string_value) like UPPER('%Текст%') 
		 ) 
	INNER JOIN card_access as ca ON ( 
		ca.object_id = c.status_id AND 
		ca.template_id = c.template_id AND 
		ca.permission_type in (2) 
	) 
	LEFT JOIN a_v_name_test as avUsr ON ( 
		avUsr.card_id = c.card_id AND 
		avUsr.attribute_code = ca.person_attribute_code AND 
		avUsr.number_value = 12100  
	) 
WHERE (1=1) 
AND(
		(ca.role_code IS NULL AND ca.person_attribute_code IS NULL) 
		OR (  
			(  
				ca.role_code is NULL  
				OR EXISTS(
						select 1  
						from person_role pr LEFT JOIN person_role_template prt
							on pr.prole_id = prt.prole_id
						where
							COALESCE(prt.template_id, ca.template_id) = ca.template_id
							and pr.role_code = ca.role_code
							and pr.person_id = 12100  
				)   
			) 
			AND (  
				ca.person_attribute_code IS NULL  
				OR avUsr.attribute_code is not NULL  
			) 
		)   
)  
		 AND c.template_id = (222) 
		 AND c.status_id IN (4,7) 
 GROUP BY c.card_id
	 ORDER BY min(avSort_1.template_name_rus), min(avSort_2.string_value)



результат:

Код: plaintext
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.
"Sort  (cost=10000363536.06..10000363536.16 rows=41 width=167) (actual time=9646.883..9646.883 rows=27 loops=1)"
"  Sort Key: (min((avsort_1.template_name_rus)::text)), (min((avsort_2.string_value)::text))"
"  Sort Method: quicksort  Memory: 28kB"
"  Buffers: shared hit=150048 read=5"
"  ->  GroupAggregate  (cost=10000360738.64..10000363534.96 rows=41 width=167) (actual time=9644.935..9646.444 rows=27 loops=1)"
"        Buffers: shared hit=150043 read=5"
"        ->  Nested Loop Left Join  (cost=10000360738.64..10000363534.24 rows=41 width=167) (actual time=9644.865..9646.396 rows=27 loops=1)"
"              Buffers: shared hit=150043 read=5"
"              ->  Merge Left Join  (cost=10000360738.21..10000363468.15 rows=41 width=45) (actual time=9644.846..9646.156 rows=27 loops=1)"
"                    Merge Cond: (c.card_id = avusr.card_id)"
"                    Join Filter: ((avusr.attribute_code)::text = (ca.person_attribute_code)::text)"
"                    Filter: (((ca.role_code IS NULL) AND (ca.person_attribute_code IS NULL)) OR (((ca.role_code IS NULL) OR (SubPlan 1)) AND ((ca.person_attribute_code IS NULL) OR (avusr.attribute_code IS NOT NULL))))"
"                    Rows Removed by Filter: 115"
"                    Buffers: shared hit=149934 read=5"
"                    ->  Nested Loop  (cost=10000360729.75..10000363380.99 rows=59 width=72) (actual time=9644.733..9645.092 rows=142 loops=1)"
"                          Buffers: shared hit=149374"
"                          ->  Nested Loop  (cost=10000360729.47..10000363364.31 rows=52 width=55) (actual time=9644.706..9644.782 rows=27 loops=1)"
"                                Buffers: shared hit=149293"
"                                ->  Merge Join  (cost=10000360729.33..10000363355.50 rows=52 width=17) (actual time=9644.688..9644.731 rows=27 loops=1)"
"                                      Merge Cond: (c.card_id = av99.card_id)"
"                                      Buffers: shared hit=149291"
"                                      ->  Sort  (cost=10000134868.07..10000136180.27 rows=524880 width=17) (actual time=65.124..65.132 rows=27 loops=1)"
"                                            Sort Key: c.card_id"
"                                            Sort Method: quicksort  Memory: 27kB"
"                                            Buffers: shared hit=77127"
"                                            ->  Seq Scan on a_v_card_test c  (cost=10000000000.00..10000085000.20 rows=524880 width=17) (actual time=36.922..65.091 rows=27 loops=1)"
"                                                  Filter: ((status_id = ANY ('{4,7}'::numeric[])) AND (template_id = 222::numeric))"
"                                                  Buffers: shared hit=77127"
"                                      ->  Sort  (cost=225861.24..225861.94 rows=278 width=6) (actual time=9579.554..9579.557 rows=54 loops=1)"
"                                            Sort Key: av99.card_id"
"                                            Sort Method: quicksort  Memory: 27kB"
"                                            Buffers: shared hit=72164"
"                                            ->  Index Scan using a_v_attribute_code on a_v_name_test av99  (cost=0.43..225849.96 rows=278 width=6) (actual time=21.399..9579.397 rows=58 loops=1)"
"                                                  Index Cond: ((attribute_code)::text = 'NAME'::text)"
"                                                  Filter: ((string_value IS NOT NULL) AND (upper((string_value)::text) ~~ '%Текст%'::text))"
"                                                  Rows Removed by Filter: 2819921"
"                                                  Buffers: shared hit=72164"
"                                ->  Materialize  (cost=0.14..8.17 rows=1 width=43) (actual time=0.001..0.001 rows=1 loops=27)"
"                                      Buffers: shared hit=2"
"                                      ->  Index Scan using test_template_template_id_idx on template avsort_1  (cost=0.14..8.16 rows=1 width=43) (actual time=0.013..0.013 rows=1 loops=1)"
"                                            Index Cond: (template_id = 222::numeric)"
"                                            Buffers: shared hit=2"
"                          ->  Index Scan using card_access_idx_tmpl_objid on card_access ca  (cost=0.29..0.31 rows=1 width=32) (actual time=0.007..0.010 rows=5 loops=27)"
"                                Index Cond: ((object_id = c.status_id) AND (template_id = 222::numeric))"
"                                Filter: (permission_type = 2::numeric)"
"                                Rows Removed by Filter: 4"
"                                Buffers: shared hit=81"
"                    ->  Sort  (cost=8.46..8.46 rows=1 width=11) (actual time=0.047..0.047 rows=0 loops=1)"
"                          Sort Key: avusr.card_id"
"                          Sort Method: quicksort  Memory: 25kB"
"                          Buffers: shared read=3"
"                          ->  Index Scan using a_v_number_value on a_v_name_test avusr  (cost=0.43..8.45 rows=1 width=11) (actual time=0.034..0.034 rows=0 loops=1)"
"                                Index Cond: (number_value = 12100::numeric)"
"                                Buffers: shared read=3"
"                    SubPlan 1"
"                      ->  Nested Loop Left Join  (cost=0.70..78.54 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=142)"
"                            Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                            Rows Removed by Filter: 1"
"                            Buffers: shared hit=560 read=2"
"                            ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.90 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=142)"
"                                  Index Cond: (person_id = 12100::numeric)"
"                                  Filter: ((role_code)::text = (ca.role_code)::text)"
"                                  Rows Removed by Filter: 4"
"                                  Buffers: shared hit=426"
"                            ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..65.24 rows=32 width=12) (actual time=0.007..0.008 rows=5 loops=34)"
"                                  Index Cond: (pr.prole_id = prole_id)"
"                                  Buffers: shared hit=134 read=2"
"              ->  Index Scan using a_v_card_id on a_v_name_test avsort_2  (cost=0.43..1.60 rows=1 width=128) (actual time=0.008..0.008 rows=1 loops=27)"
"                    Index Cond: (card_id = c.card_id)"
"                    Filter: ((attribute_code)::text = 'NAME'::text)"
"                    Buffers: shared hit=109"
"Total runtime: 9647.057 ms"

при
Код: plsql
1.
SET enable_seqscan TO off;


если
Код: plsql
1.
SET enable_seqscan TO on;

то для
Код: plsql
1.
2.
3.
4.
5.
6.
JOIN a_v_name_test av99 ON ( 
					(av99.card_id = c.card_id) 
					AND ( av99.attribute_code IN ('NAME'))
					AND ( av99.string_value IS NOT NULL)
					AND UPPER(av99.string_value) like UPPER('%Текст%') 
		 )


индексы не используются и время не изменяется, также на данный отрезок скрипта идет основная потеря времени(без него в 3-4 раза быстрее). Параметры таблицы(имя/кол-во строк/размер):
Код: plaintext
"dbmi_trunk.a_v_name_test";2819757;"1808 MB"
Индексы:
Код: plsql
1.
2.
3.
4.
create index a_v_card_id on a_v_name_test USING btree (card_id);
create index a_v_attribute_code on a_v_name_test USING btree (attribute_code);
create index a_v_string_value on a_v_name_test USING btree (string_value);
create index a_v_number_value on a_v_name_test USING btree (number_value);


Некоторые параметры БД:
Код: plaintext
1.
2.
3.
4.
"effective_cache_size";"2359296";"8kB"
"maintenance_work_mem";"2097152";"kB"
"shared_buffers";"2097152";"8kB"
"work_mem";"1562624";"kB"
...
Рейтинг: 0 / 0
28.10.2014, 12:19:48
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38789167
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
its_me,

попробуйте добавить индекс (template_id, status_id) в таблицу a_v_card_test.

Код: sql
1.
2.
3.
Seq Scan on a_v_card_test c  (cost=10000000000.00..10000085000.20 rows=524880 width=17) (actual time=36.922..65.091 rows=27 loops=1)"
"                                                  Filter: ((status_id = ANY ('{4,7}'::numeric[])) AND (template_id = 222::numeric))"
"                                                  Buffers: shared hit=77127"



планнер тут очень сильно ошибается в количестве строк, и из-за этого возможно не самый оптимальный план получается.
...
Рейтинг: 0 / 0
28.10.2014, 12:36:02
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38789180
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Alexius,

5+, видать неусмотрел я. Total time: 3с, Еще сократить можно интересно...
...
Рейтинг: 0 / 0
28.10.2014, 12:45:10
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38789186
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
its_me,

наверняка можно, надо новый план смотреть.
...
Рейтинг: 0 / 0
28.10.2014, 12:49:07
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38789331
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Код: plaintext
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.
"Sort  (cost=12180.90..12180.91 rows=1 width=166) (actual time=2859.780..2859.783 rows=27 loops=1)"
"  Sort Key: (min((avsort_1.template_name_rus)::text)), (min((avsort_2.string_value)::text))"
"  Sort Method: quicksort  Memory: 28kB"
"  Buffers: shared hit=1437127"
"  ->  HashAggregate  (cost=12180.88..12180.89 rows=1 width=166) (actual time=2859.552..2859.558 rows=27 loops=1)"
"        Buffers: shared hit=1437122"
"        ->  Nested Loop Left Join  (cost=19.48..12180.87 rows=1 width=166) (actual time=329.616..2859.456 rows=27 loops=1)"
"              Buffers: shared hit=1437122"
"              ->  Nested Loop  (cost=19.05..12173.15 rows=1 width=44) (actual time=329.607..2859.258 rows=27 loops=1)"
"                    Buffers: shared hit=1437014"
"                    ->  Nested Loop  (cost=19.05..12168.61 rows=1 width=11) (actual time=329.570..2858.491 rows=27 loops=1)"
"                          Buffers: shared hit=1436933"
"                          ->  Hash Left Join  (cost=18.62..5347.54 rows=882 width=11) (actual time=328.365..2453.451 rows=51076 loops=1)"
"                                Hash Cond: ((c.card_id = avusr.card_id) AND ((ca.person_attribute_code)::text = (avusr.attribute_code)::text))"
"                                Filter: (((ca.role_code IS NULL) AND (ca.person_attribute_code IS NULL)) OR (((ca.role_code IS NULL) OR (SubPlan 1)) AND ((ca.person_attribute_code IS NULL) OR (avusr.attribute_code IS NOT NULL))))"
"                                Rows Removed by Filter: 227692"
"                                Buffers: shared hit=1232410"
"                                ->  Nested Loop  (cost=10.16..5251.02 rows=1267 width=38) (actual time=8.026..401.883 rows=278768 loops=1)"
"                                      Buffers: shared hit=98244"
"                                      ->  Index Scan using card_access_idx_tmpl_perm_objid on card_access ca  (cost=0.29..61.50 rows=16 width=32) (actual time=0.015..0.037 rows=16 loops=1)"
"                                            Index Cond: ((permission_type = 2::numeric) AND (template_id = 222::numeric))"
"                                            Buffers: shared hit=3"
"                                      ->  Bitmap Heap Scan on a_v_card_test c  (cost=9.88..323.55 rows=80 width=16) (actual time=5.994..20.660 rows=17423 loops=16)"
"                                            Recheck Cond: ((template_id = 222::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
"                                            Buffers: shared hit=98241"
"                                            ->  Bitmap Index Scan on a_v_card_templ_stat  (cost=0.00..9.86 rows=80 width=0) (actual time=4.844..4.844 rows=17423 loops=16)"
"                                                  Index Cond: ((template_id = 222::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
"                                                  Buffers: shared hit=1353"
"                                ->  Hash  (cost=8.45..8.45 rows=1 width=11) (actual time=0.011..0.011 rows=0 loops=1)"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 0kB"
"                                      Buffers: shared hit=3"
"                                      ->  Index Scan using a_v_number_value on a_v_name_test avusr  (cost=0.43..8.45 rows=1 width=11) (actual time=0.009..0.009 rows=0 loops=1)"
"                                            Index Cond: (number_value = 12100::numeric)"
"                                            Buffers: shared hit=3"
"                                SubPlan 1"
"                                  ->  Nested Loop Left Join  (cost=0.70..78.54 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=278768)"
"                                        Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                                        Rows Removed by Filter: 2"
"                                        Buffers: shared hit=1134160"
"                                        ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.90 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=278768)"
"                                              Index Cond: (person_id = 12100::numeric)"
"                                              Filter: ((role_code)::text = (ca.role_code)::text)"
"                                              Rows Removed by Filter: 4"
"                                              Buffers: shared hit=836304"
"                                        ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..65.24 rows=32 width=12) (actual time=0.006..0.007 rows=7 loops=74464)"
"                                              Index Cond: (pr.prole_id = prole_id)"
"                                              Buffers: shared hit=297856"
"                          ->  Index Scan using a_v_card_id on a_v_name_test av99  (cost=0.43..7.72 rows=1 width=6) (actual time=0.007..0.007 rows=0 loops=51076)"
"                                Index Cond: (card_id = c.card_id)"
"                                Filter: ((string_value IS NOT NULL) AND ((attribute_code)::text = 'NAME'::text) AND (upper((string_value)::text) ~~ '%АДМИНИСТРАЦИЯ ОСТРОВСКОГО РАЙОНА%'::text))"
"                                Rows Removed by Filter: 1"
"                                Buffers: shared hit=204523"
"                    ->  Seq Scan on template avsort_1  (cost=0.00..4.53 rows=1 width=43) (actual time=0.023..0.026 rows=1 loops=27)"
"                          Filter: (template_id = 222::numeric)"
"                          Rows Removed by Filter: 121"
"                          Buffers: shared hit=81"
"              ->  Index Scan using a_v_card_id on a_v_name_test avsort_2  (cost=0.43..7.72 rows=1 width=128) (actual time=0.005..0.005 rows=1 loops=27)"
"                    Index Cond: (card_id = c.card_id)"
"                    Filter: ((attribute_code)::text = 'NAME'::text)"
"                    Buffers: shared hit=108"
"Total runtime: 2859.954 ms"
...
Рейтинг: 0 / 0
28.10.2014, 14:13:51
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38790253
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
its_me,

хм, можно попробовать создать индекс в a_v_name_test на поле UPPER(av99.string_value) используя расширение pg_trgm .
...
Рейтинг: 0 / 0
29.10.2014, 09:59:03
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38790302
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
CREATE INDEX a_v_name_test_idx ON a_v_name_test USING gist (UPPER(string_value) gist_trgm_ops) ?
...
Рейтинг: 0 / 0
29.10.2014, 10:18:28
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38790326
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
its_me,

да. если запросы по этому полю селективные, то должно хватить. если attribute_code == 'NAME' всегда в этом запросе, то можно частичный индекс сделать по этому условию.
...
Рейтинг: 0 / 0
29.10.2014, 10:31:17
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38790460
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Код: plsql
1.
CREATE INDEX test_pg_trgm_string_value_idx ON attribute_value USING gist (UPPER(string_value) gist_trgm_ops) WHERE attribute_code = 'NAME';


Супер - в 10 раз, но возникала другая проблема если ввести например вместо AND UPPER(av99.string_value) like UPPER('%Администрация островского района%') вот это AND UPPER(av99.string_value) like UPPER('%гу%'), то он почему то не использует данный индекс а использует обычный по string_value.
...
Рейтинг: 0 / 0
29.10.2014, 11:48:57
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38790469
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
вот это использует:
Код: plsql
1.
2.
3.
4.
5.
CREATE INDEX attribute_value_name_string_value_idx
  ON attribute_value
  USING btree
  (card_id, string_value COLLATE pg_catalog."default")
  WHERE attribute_code::text = 'NAME'::text;
...
Рейтинг: 0 / 0
29.10.2014, 11:51:07
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38790474
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Если убрать этот индекс, то делает по новому, но долго...
...
Рейтинг: 0 / 0
29.10.2014, 11:55:28
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38790557
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
its_me,

естественно, запрос должен быть селективным чтобы индекс эффективно использовался.

attribute_value_name_string_value_idx - этот индекс для данного запроса не оптимальный, поле string_value из него никогда не будет использоваться, можно его отсюда выкинуть (если другие запросы его не используют).
...
Рейтинг: 0 / 0
29.10.2014, 12:40:39
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38790713
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
а для "гу" можно что сделать?
...
Рейтинг: 0 / 0
29.10.2014, 14:15:04
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791071
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
Вообще чисто по плану, очевидно что правильный план должен сначала фильтровать a_v_card_test по

Filter: ((status_id = ANY ('{4,7}'::numeric[])) AND (template_id = 222::numeric))"

Получив 27 записей, а затем бежать index scan'ом по av99 и условию

(c.card_id = av99.card_id)

Получив 27 записей и потом уже фильтровать

(av99.attribute_code IN ('NAME')
AND ( av99.string_value IS NOT NULL)
AND UPPER(av99.string_value) like UPPER('%Текст%')

Получив 27 записей.

Другое дело почему он так не делает. А не делает это потому что статистика у него идет в разнос, почему-то

-> Seq Scan on a_v_card_test c (cost=10000000000.00..10000085000.20 rows=524880 width=17) (actual time=36.922..65.091 rows=27 loops=1)"
" Filter: ((status_id = ANY ('{4,7}'::numeric[])) AND (template_id = 222::numeric))"
" Buffers: shared hit=77127"

он думает что с таким фильтром 524880 записей (может проблема в ANY, может статистика не обновлена, но похоже нужно смотреть на данные).

А тут наоборот (хотя тут понятнее - LIKE всегда жесткий случай)

-> Index Scan using a_v_attribute_code on a_v_name_test av99 (cost=0.43..225849.96 rows=278 width=6) (actual time=21.399..9579.397 rows=58 loops=1)"
" Index Cond: ((attribute_code)::text = 'NAME'::text)"
" Filter: ((string_value IS NOT NULL) AND (upper((string_value)::text) ~~ '%Текст%'::text))"
" Rows Removed by Filter: 2819921"
" Buffers: shared hit=72164"

Кстати по card_id индексы есть?
...
Рейтинг: 0 / 0
29.10.2014, 17:47:55
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791086
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
Nitro_Junkie,

Хотя со вторым не такая большая ошибка. Там не сильное отличие. Проблема с первой частью, в которой она думает 580000 (индекс то по card_id у вас явно есть), и по этому не хочет index scan делать хотя надо бы.

Попробуйте разбить на 2 запроса в одном из которых будет status = 4, а во втором status = 7, а потом UNION'ом соедините.
...
Рейтинг: 0 / 0
29.10.2014, 17:59:58
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791399
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Nitro_Junkie,

Индекс по card_id везде есть, сами запросы неизменны(надо подстраиваться под них).
...
Рейтинг: 0 / 0
30.10.2014, 09:06:25
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791437
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
its_meNitro_Junkie,

Индекс по card_id везде есть, сами запросы неизменны(надо подстраиваться под них).

А по status_id и template_id ? Хотя корень проблемы конечно в неправильной статистике фильтра.

А вы попробуйте сбоку запустить запрос с не IN (4,7) а просто status_id = 4. И попробуйте ALTER TABLE ... SET STATISTICS для status_id и template_id поставить 10000 (после чего ANALYZE только не забудьте выполнить)
...
Рейтинг: 0 / 0
30.10.2014, 09:41:30
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791457
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
Nitro_Junkie,

Черт, смотрел на старый план. Но в новом все та же проблема. Попробуйте в принципе default_statistics_target увеличить.

Но не поможет :( Проблема в том что в postgres нет cross-column статистики. И он тупо перемножает selectivity для template_id и status_id. А они у вас на самом деле сильно коррелированы. Без переписывания запроса без шансов. :( Да и с переписыванием в общем случае, единственное что можно делать "объединять" колонки в одну (ROW'ом или чем-то еще) и по ней join'ить (но это по сути ручное управление выполнением join'ов).
...
Рейтинг: 0 / 0
30.10.2014, 09:53:51
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791475
Nitro_Junkie
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nitro_Junkie
Участник
Nitro_Junkie,

Собсно проблема всех СУБД - сильная оптимистичность. Они считают что если они не знают как коррелированы предикаты, будем считать что они никак не коррелированы, перемножают вероятности и уменьшают статистику (для сравнения пессимистичный сценарий вместо a1*a2 брать MAX(a1,a2). При этом если не угадал в меньшую сторону получишь NESTED LOOP JOIN (n*m) вместо HASH (n*logm), или JOIN двух таблиц по 10к записей (и хорошо если с Removed Filter на 990000 записей, а то может ее целиком в памяти материализовать) и т.п., а значит запрос повиснет (хорошо если сервер не упадет). Если же не угадал в большую то в n*m никогда не свалишься, в худшем случае будет n вместо m (но опять таки в жизни предикаты гораздо чаще коррелированы чем нет).
...
Рейтинг: 0 / 0
30.10.2014, 10:03:41
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791511
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Вообщем реально изменил ситуацию пока только pg_trgm и существенно, так как запросы неизменны думаю существенно лучше никак.
...
Рейтинг: 0 / 0
30.10.2014, 11:57:10
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791627
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
its_me,

план нужно смотреть, без него гадать только можно. сильно быстрее врядли получится, какие-то индексы возможно стоит поправить.
...
Рейтинг: 0 / 0
30.10.2014, 13:25:38
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791718
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Код: plsql
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.
EXPLAIN (ANALYZE,BUFFERS)
SELECT c.card_id
FROM  card c 
 
	JOIN template AS avSort_1 ON avSort_1.template_id = c.template_id

	LEFT JOIN attribute_value AS avSort_2 
		ON avSort_2.card_id = c.card_id
		AND avSort_2.attribute_code='NAME' 

		 JOIN attribute_value av99 ON ( 
					(av99.card_id = c.card_id) 
					AND ( av99.attribute_code IN ('NAME'))
					AND ( av99.string_value IS NOT NULL)
					AND UPPER(av99.string_value) like UPPER('%Администрация островского района%') 
		 ) 
	INNER JOIN card_access as ca ON ( 
		ca.object_id = c.status_id AND 
		ca.template_id = c.template_id AND 
		ca.permission_type in (2) 
	) 
	LEFT JOIN attribute_value as avUsr ON ( 
		avUsr.card_id = c.card_id AND 
		avUsr.attribute_code = ca.person_attribute_code AND 
		avUsr.number_value = 12100  
	) 
WHERE (1=1) 
AND(
		(ca.role_code IS NULL AND ca.person_attribute_code IS NULL) 
		OR (  
			(  
				ca.role_code is NULL  
				OR EXISTS(
						select 1  
						from person_role pr LEFT JOIN person_role_template prt
							on pr.prole_id = prt.prole_id
						where
							COALESCE(prt.template_id, ca.template_id) = ca.template_id
							and pr.role_code = ca.role_code
							and pr.person_id = 12100  
				)   
			) 
			AND (  
				ca.person_attribute_code IS NULL  
				OR avUsr.attribute_code is not NULL  
			) 
		)   
)  
		 AND c.template_id = (222) 
		 AND c.status_id IN (4,7) 
 GROUP BY c.card_id
	 ORDER BY min(avSort_1.template_name_rus), min(avSort_2.string_value)



Код: plaintext
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.
"Sort  (cost=1507.49..1507.49 rows=1 width=150) (actual time=260.635..260.638 rows=27 loops=1)"
"  Sort Key: (min((avsort_1.template_name_rus)::text)), (min((avsort_2.string_value)::text))"
"  Sort Method: quicksort  Memory: 28kB"
"  Buffers: shared hit=37081"
"  ->  HashAggregate  (cost=1507.47..1507.48 rows=1 width=150) (actual time=260.417..260.430 rows=27 loops=1)"
"        Buffers: shared hit=37081"
"        ->  Nested Loop Left Join  (cost=48.47..1507.46 rows=1 width=150) (actual time=257.034..260.358 rows=27 loops=1)"
"              Filter: (((ca.role_code IS NULL) AND (ca.person_attribute_code IS NULL)) OR (((ca.role_code IS NULL) OR (SubPlan 1)) AND ((ca.person_attribute_code IS NULL) OR (avusr.attribute_code IS NOT NULL))))"
"              Rows Removed by Filter: 115"
"              Buffers: shared hit=37081"
"              ->  Nested Loop  (cost=47.90..1413.27 rows=1 width=177) (actual time=256.977..259.174 rows=142 loops=1)"
"                    Buffers: shared hit=36484"
"                    ->  Nested Loop Left Join  (cost=47.62..1410.77 rows=1 width=160) (actual time=256.959..258.900 rows=27 loops=1)"
"                          Buffers: shared hit=36403"
"                          ->  Nested Loop  (cost=46.94..1349.19 rows=1 width=54) (actual time=256.939..258.626 rows=27 loops=1)"
"                                Buffers: shared hit=36240"
"                                ->  Nested Loop  (cost=46.94..1344.66 rows=1 width=16) (actual time=256.903..257.999 rows=27 loops=1)"
"                                      Buffers: shared hit=36159"
"                                      ->  Bitmap Heap Scan on attribute_value av99  (cost=46.51..1149.96 rows=23 width=6) (actual time=256.826..257.304 rows=58 loops=1)"
"                                            Recheck Cond: ((upper((string_value)::text) ~~ '%АДМИНИСТРАЦИЯ ОСТРОВСКОГО РАЙОНА%'::text) AND ((attribute_code)::text = 'NAME'::text))"
"                                            Rows Removed by Index Recheck: 11"
"                                            Filter: (string_value IS NOT NULL)"
"                                            Buffers: shared hit=35927"
"                                            ->  Bitmap Index Scan on test_pg_trgm_string_value_idx  (cost=0.00..46.50 rows=278 width=0) (actual time=256.776..256.776 rows=69 loops=1)"
"                                                  Index Cond: (upper((string_value)::text) ~~ '%АДМИНИСТРАЦИЯ ОСТРОВСКОГО РАЙОНА%'::text)"
"                                                  Buffers: shared hit=35860"
"                                      ->  Index Scan using test_card2_id_idx on card c  (cost=0.43..8.46 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=58)"
"                                            Index Cond: (card_id = av99.card_id)"
"                                            Filter: ((status_id = ANY ('{4,7}'::numeric[])) AND (template_id = 222::numeric))"
"                                            Rows Removed by Filter: 1"
"                                            Buffers: shared hit=232"
"                                ->  Seq Scan on template avsort_1  (cost=0.00..4.53 rows=1 width=43) (actual time=0.020..0.022 rows=1 loops=27)"
"                                      Filter: (template_id = 222::numeric)"
"                                      Rows Removed by Filter: 121"
"                                      Buffers: shared hit=81"
"                          ->  Index Scan using attribute_value_name_string_value_idx on attribute_value avsort_2  (cost=0.68..61.37 rows=21 width=112) (actual time=0.009..0.009 rows=1 loops=27)"
"                                Index Cond: (card_id = c.card_id)"
"                                Buffers: shared hit=163"
"                    ->  Index Scan using card_access_idx_tmpl_perm_objid on card_access ca  (cost=0.29..2.49 rows=1 width=32) (actual time=0.007..0.008 rows=5 loops=27)"
"                          Index Cond: ((permission_type = 2::numeric) AND (template_id = 222::numeric) AND (object_id = c.status_id))"
"                          Buffers: shared hit=81"
"              ->  Index Scan using xif6attribute_value on attribute_value avusr  (cost=0.57..14.36 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=142)"
"                    Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
"                    Filter: (number_value = 12100::numeric)"
"                    Rows Removed by Filter: 0"
"                    Buffers: shared hit=35"
"              SubPlan 1"
"                ->  Nested Loop Left Join  (cost=0.70..79.81 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=142)"
"                      Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                      Rows Removed by Filter: 1"
"                      Buffers: shared hit=562"
"                      ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.88 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=142)"
"                            Index Cond: (person_id = 12100::numeric)"
"                            Filter: ((role_code)::text = (ca.role_code)::text)"
"                            Rows Removed by Filter: 4"
"                            Buffers: shared hit=426"
"                      ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..66.53 rows=32 width=12) (actual time=0.006..0.007 rows=5 loops=34)"
"                            Index Cond: (pr.prole_id = prole_id)"
"                            Buffers: shared hit=136"
"Total runtime: 260.823 ms"
...
Рейтинг: 0 / 0
30.10.2014, 14:24:16
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791758
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
для "гу"(хотелось бы чтобы такое же время было):

Код: plaintext
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.
"Sort  (cost=52221.45..52221.46 rows=1 width=150) (actual time=3676.546..3676.745 rows=1660 loops=1)"
"  Sort Key: (min((avsort_1.template_name_rus)::text)), (min((avsort_2.string_value)::text))"
"  Sort Method: quicksort  Memory: 382kB"
"  Buffers: shared hit=1835187"
"  ->  HashAggregate  (cost=52221.43..52221.44 rows=1 width=150) (actual time=3642.917..3643.700 rows=1660 loops=1)"
"        Buffers: shared hit=1835182"
"        ->  Nested Loop Left Join  (cost=13.80..52221.43 rows=1 width=150) (actual time=737.358..3639.370 rows=1660 loops=1)"
"              Buffers: shared hit=1835182"
"              ->  Nested Loop  (cost=13.12..52159.84 rows=1 width=44) (actual time=737.347..3629.020 rows=1660 loops=1)"
"                    Buffers: shared hit=1825157"
"                    ->  Nested Loop Left Join  (cost=13.12..52155.31 rows=1 width=11) (actual time=737.311..3590.418 rows=1660 loops=1)"
"                          Filter: (((ca.role_code IS NULL) AND (ca.person_attribute_code IS NULL)) OR (((ca.role_code IS NULL) OR (SubPlan 1)) AND ((ca.person_attribute_code IS NULL) OR (avusr.attribute_code IS NOT NULL))))"
"                          Rows Removed by Filter: 7123"
"                          Buffers: shared hit=1820177"
"                          ->  Nested Loop  (cost=12.55..51966.93 rows=2 width=38) (actual time=9.156..3496.354 rows=8783 loops=1)"
"                                Buffers: shared hit=1782839"
"                                ->  Nested Loop  (cost=11.87..12832.18 rows=3468 width=38) (actual time=8.690..494.023 rows=279278 loops=1)"
"                                      Buffers: shared hit=98143"
"                                      ->  Index Scan using card_access_idx_tmpl_perm_objid on card_access ca  (cost=0.29..61.50 rows=16 width=32) (actual time=0.045..0.071 rows=16 loops=1)"
"                                            Index Cond: ((permission_type = 2::numeric) AND (template_id = 222::numeric))"
"                                            Buffers: shared hit=3"
"                                      ->  Bitmap Heap Scan on card c  (cost=11.59..796.03 rows=214 width=16) (actual time=6.434..26.109 rows=17455 loops=16)"
"                                            Recheck Cond: ((template_id = 222::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
"                                            Buffers: shared hit=98140"
"                                            ->  Bitmap Index Scan on template_and_status_idx  (cost=0.00..11.54 rows=214 width=0) (actual time=5.222..5.222 rows=17455 loops=16)"
"                                                  Index Cond: ((template_id = 222::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
"                                                  Buffers: shared hit=1109"
"                                ->  Index Scan using attribute_value_name_string_value_idx on attribute_value av99  (cost=0.68..11.27 rows=1 width=6) (actual time=0.010..0.010 rows=0 loops=279278)"
"                                      Index Cond: ((card_id = c.card_id) AND (string_value IS NOT NULL))"
"                                      Filter: (upper((string_value)::text) ~~ '%ГУ%'::text)"
"                                      Rows Removed by Filter: 1"
"                                      Buffers: shared hit=1684696"
"                          ->  Index Scan using xif6attribute_value on attribute_value avusr  (cost=0.57..14.36 rows=1 width=21) (actual time=0.002..0.002 rows=0 loops=8783)"
"                                Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
"                                Filter: (number_value = 12100::numeric)"
"                                Rows Removed by Filter: 0"
"                                Buffers: shared hit=2417"
"                          SubPlan 1"
"                            ->  Nested Loop Left Join  (cost=0.70..79.81 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=8783)"
"                                  Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                                  Rows Removed by Filter: 1"
"                                  Buffers: shared hit=34921"
"                                  ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.88 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=8783)"
"                                        Index Cond: (person_id = 12100::numeric)"
"                                        Filter: ((role_code)::text = (ca.role_code)::text)"
"                                        Rows Removed by Filter: 4"
"                                        Buffers: shared hit=26349"
"                                  ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..66.53 rows=32 width=12) (actual time=0.007..0.008 rows=6 loops=2143)"
"                                        Index Cond: (pr.prole_id = prole_id)"
"                                        Buffers: shared hit=8572"
"                    ->  Seq Scan on template avsort_1  (cost=0.00..4.53 rows=1 width=43) (actual time=0.020..0.022 rows=1 loops=1660)"
"                          Filter: (template_id = 222::numeric)"
"                          Rows Removed by Filter: 121"
"                          Buffers: shared hit=4980"
"              ->  Index Scan using attribute_value_name_string_value_idx on attribute_value avsort_2  (cost=0.68..61.37 rows=21 width=112) (actual time=0.005..0.005 rows=1 loops=1660)"
"                    Index Cond: (card_id = c.card_id)"
"                    Buffers: shared hit=10025"
"Total runtime: 3677.234 ms"
...
Рейтинг: 0 / 0
30.10.2014, 14:47:26
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791845
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
its_me,

ради интереса можно создать gin индекс вместо gist

можно попробовать выполнить запрос с set enable_nestloop = off

ну и индекс в template по template_id надо добавить (раньше был же).

что показывает
Код: sql
1.
explain analyze select count(*) from a_v_name_test where attribute_code IN ('NAME') AND string_value IS NOT NULL AND UPPER(av99.string_value) like UPPER('%ГУ%');


?
...
Рейтинг: 0 / 0
30.10.2014, 15:40:52
| Ответить | Цитировать | Написать  
Как сократить время выполнения скрипта?
    #38791859
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Alexiusits_me,

ради интереса можно создать gin индекс вместо gist

можно попробовать выполнить запрос с set enable_nestloop = off

ну и индекс в template по template_id надо добавить (раньше был же).

что показывает
Код: sql
1.
explain analyze select count(*) from a_v_name_test where attribute_code IN ('NAME') AND string_value IS NOT NULL AND UPPER(av99.string_value) like UPPER('%ГУ%');


?

1. Пробовал, gin - в 10 раз дольше.
2. set enable_nestloop = off - с настройками не хочется экспериментировать, посмотрю...
3. индекс никуда не делся
4. показывает просто другое время(в 10 раз дольше чем надо)
...
Рейтинг: 0 / 0
30.10.2014, 15:47:21
| Ответить | Цитировать | Написать  
25 сообщений из 25, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как сократить время выполнения скрипта?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=120&tid=1998386]:
 0ms
get settings:
 4ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 16ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 39ms
get tp. blocked users:
 1ms
others: 204ms
total:  288ms
созд. / загр.: 288ms
x
x
Закрыть


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