Как оптимизировать время выполнения скрипта? / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как оптимизировать время выполнения скрипта?

25 сообщений из 31, страница 1 из 2

все

Как оптимизировать время выполнения скрипта?

#38818405

its_me

Гость

Один и тот же скрипт с разным условием выбирает разные методы выполнения и как следствие разное время на выходе, как это исправить оставив метод с минимальным временем выполнения?

Инфа:

Скрипт 1:

Код: 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.

explain analyze SELECT count(DISTINCT c.card_id) 
FROM  card c 

		 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 = 7820  
	) 
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 = 7820  
				) -- OR EXISTS  
			) --   
			AND (  
				ca.person_attribute_code IS NULL  
				OR avUsr.attribute_code is not NULL  
			) -- AND  
		) -- OR  
) -- AND 

		 AND c.template_id = (2300) 

		 AND c.status_id IN (4,7);

Результат:

Код: 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.

"Aggregate  (cost=1511.95..1511.96 rows=1 width=6) (actual time=7435.362..7435.362 rows=1 loops=1)"
"  ->  Nested Loop Left Join  (cost=51.90..1511.95 rows=1 width=6) (actual time=6291.725..7434.568 rows=1 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))))"
"        ->  Nested Loop  (cost=51.33..1417.82 rows=1 width=33) (actual time=6291.717..7434.560 rows=1 loops=1)"
"              ->  Nested Loop  (cost=51.05..1416.65 rows=1 width=16) (actual time=6291.684..7434.523 rows=1 loops=1)"
"                    ->  Bitmap Heap Scan on attribute_value av99  (cost=50.62..1213.49 rows=24 width=6) (actual time=1260.930..3970.662 rows=582949 loops=1)"
"                          Recheck Cond: ((upper((string_value)::text) ~~ '%ПИСЬ%'::text) AND ((attribute_code)::text = 'NAME'::text))"
"                          Rows Removed by Index Recheck: 10"
"                          Filter: (string_value IS NOT NULL)"
"                          ->  Bitmap Index Scan on test_pg_trgm_string_value_idx  (cost=0.00..50.61 rows=293 width=0) (actual time=1098.093..1098.093 rows=584965 loops=1)"
"                                Index Cond: (upper((string_value)::text) ~~ '%ПИСЬ%'::text)"
"                    ->  Index Scan using test_card2_id_idx on card c  (cost=0.43..8.46 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=582949)"
"                          Index Cond: (card_id = av99.card_id)"
"                          Filter: ((status_id = ANY ('{4,7}'::numeric[])) AND (template_id = 2300::numeric))"
"                          Rows Removed by Filter: 1"
"              ->  Index Scan using card_access_idx_tmpl_objid on card_access ca  (cost=0.29..1.17 rows=1 width=32) (actual time=0.019..0.023 rows=1 loops=1)"
"                    Index Cond: ((object_id = c.status_id) AND (template_id = 2300::numeric))"
"                    Filter: (permission_type = 2::numeric)"
"                    Rows Removed by Filter: 3"
"        ->  Index Scan using xif6attribute_value on attribute_value avusr  (cost=0.57..16.36 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=1)"
"              Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
"              Filter: (number_value = 7820::numeric)"
"        SubPlan 1"
"          ->  Nested Loop Left Join  (cost=0.70..77.76 rows=1 width=0) (never executed)"
"                Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.76 rows=1 width=6) (never executed)"
"                      Index Cond: (person_id = 7820::numeric)"
"                      Filter: ((role_code)::text = (ca.role_code)::text)"
"                ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..64.60 rows=32 width=12) (never executed)"
"                      Index Cond: (pr.prole_id = prole_id)"
"Total runtime: 7435.589 ms"

Скрипт 2:

Код: 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.

explain analyze SELECT count(DISTINCT c.card_id) 
FROM  card c 

		 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 = 7820  
	) 
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 = 7820  
				) -- OR EXISTS  
			) --   
			AND (  
				ca.person_attribute_code IS NULL  
				OR avUsr.attribute_code is not NULL  
			) -- AND  
		) -- OR  
) -- AND 

		 AND c.template_id = (2300) 

		 AND c.status_id IN (4,7);

Результат:

Код: 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.

"Aggregate  (cost=3300.11..3300.12 rows=1 width=6) (actual time=2.329..2.329 rows=1 loops=1)"
"  ->  Nested Loop Left Join  (cost=14.68..3300.10 rows=1 width=6) (actual time=0.257..2.292 rows=6 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))))"
"        ->  Nested Loop  (cost=14.12..3205.98 rows=1 width=33) (actual time=0.250..2.280 rows=6 loops=1)"
"              ->  Nested Loop  (cost=13.44..887.25 rows=184 width=33) (actual time=0.114..0.555 rows=214 loops=1)"
"                    ->  Bitmap Heap Scan on card_access ca  (cost=4.45..43.07 rows=16 width=32) (actual time=0.022..0.027 rows=29 loops=1)"
"                          Recheck Cond: ((permission_type = 2::numeric) AND (template_id = 2300::numeric))"
"                          ->  Bitmap Index Scan on card_access_idx_tmpl_perm_objid  (cost=0.00..4.45 rows=16 width=0) (actual time=0.016..0.016 rows=29 loops=1)"
"                                Index Cond: ((permission_type = 2::numeric) AND (template_id = 2300::numeric))"
"                    ->  Bitmap Heap Scan on card c  (cost=8.99..52.65 rows=11 width=16) (actual time=0.011..0.015 rows=7 loops=29)"
"                          Recheck Cond: ((template_id = 2300::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
"                          ->  Bitmap Index Scan on template_and_status_idx  (cost=0.00..8.98 rows=11 width=0) (actual time=0.006..0.006 rows=7 loops=29)"
"                                Index Cond: ((template_id = 2300::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
"              ->  Index Scan using attribute_value_name_string_value_idx on attribute_value av99  (cost=0.68..12.59 rows=1 width=6) (actual time=0.008..0.008 rows=0 loops=214)"
"                    Index Cond: ((card_id = c.card_id) AND (string_value IS NOT NULL))"
"                    Filter: (upper((string_value)::text) ~~ '%ПИС%'::text)"
"                    Rows Removed by Filter: 1"
"        ->  Index Scan using xif6attribute_value on attribute_value avusr  (cost=0.57..16.36 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=6)"
"              Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
"              Filter: (number_value = 7820::numeric)"
"        SubPlan 1"
"          ->  Nested Loop Left Join  (cost=0.70..77.76 rows=1 width=0) (never executed)"
"                Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.76 rows=1 width=6) (never executed)"
"                      Index Cond: (person_id = 7820::numeric)"
"                      Filter: ((role_code)::text = (ca.role_code)::text)"
"                ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..64.60 rows=32 width=12) (never executed)"
"                      Index Cond: (pr.prole_id = prole_id)"
"Total runtime: 2.462 ms"

Индексы:

(данный индекс создавался для другого похожего запроса и оправдал себя в нём на 2000%, в отличие от данного)

Код: plsql

1.
2.
3.
4.
5.

CREATE INDEX test_pg_trgm_string_value_idx
  ON attribute_value
  USING gist
  (upper(string_value::text) COLLATE pg_catalog."default" gist_trgm_ops)
  WHERE attribute_code::text = 'NAME'::text;

Код: 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;

Разница в скриптах в отличие от времени их выполнения смешная:

Код: plaintext

%пись%

Код: plaintext

%пис%

...

Рейтинг:

0 / 0

27.11.2014, 14:20:14

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818450

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

its_me,

тут явно неудачно оценивается в первом запросе селективность триграмного индекса
можно отключить его использование костылем через замену

Код: plsql

AND UPPER(av99.string_value) like UPPER('%пись%')

на например

Код: plsql

UPPER(av99.string_value)||'' like UPPER('%пись%')

--Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

27.11.2014, 15:00:12

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818451

its_me

Гость

Забыл про один момент - скрипт неизменен(вшит в приложение безвозвратно).

...

Рейтинг:

0 / 0

27.11.2014, 15:02:32

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818456

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

its_meЗабыл про один момент - скрипт неизменен(вшит в приложение безвозвратно).

никак или удалить trigram index

--Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

27.11.2014, 15:11:07

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818466

its_me

Гость

Maxim Bogukits_meЗабыл про один момент - скрипт неизменен(вшит в приложение безвозвратно).

никак или удалить trigram index

--Maxim Boguk
www.postgresql-consulting.ru

а причину почему он так меняет своё настроение от одной буквы(варианты, предположения)?

...

Рейтинг:

0 / 0

27.11.2014, 15:19:01

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818515

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

its_meMaxim Bogukпропущено...

никак или удалить trigram index

--Maxim Boguk
www.postgresql-consulting.ru

а причину почему он так меняет своё настроение от одной буквы(варианты, предположения)?

прогоните:

explain analyze select count(*) from attribute_value where UPPER(string_value) like UPPER('%пис%');
explain analyze select count(*) from attribute_value where UPPER(string_value) like UPPER('%пись%');

explain analyze select count(*) from attribute_value where UPPER(string_value) like UPPER('%пис%') and attribute_code IN ('NAME');
explain analyze select count(*) from attribute_value where UPPER(string_value) like UPPER('%пись%') and attribute_code IN ('NAME');

тогда я что то смогу сказать.

Но на практике использование attribute_value таблиц - к проблемам на любой базе ВСЕГДА И БЕЗ ВАРИАНТОВ.
Это самый худший известный мне дизайн-антипаттерн для базы.

--Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

27.11.2014, 15:59:22

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818541

its_me

Гость

Код: plsql

explain analyze select count(*) from attribute_value where UPPER(string_value) like UPPER('%пис%');

Код: plaintext

1.
2.
3.
4.
5.

"Aggregate  (cost=2658611.39..2658611.40 rows=1 width=0) (actual time=60314.300..60314.300 rows=1 loops=1)"
"  ->  Seq Scan on attribute_value  (cost=0.00..2658529.12 rows=32908 width=0) (actual time=0.064..60107.549 rows=1327281 loops=1)"
"        Filter: (upper((string_value)::text) ~~ '%ПИС%'::text)"
"        Rows Removed by Filter: 97970140"
"Total runtime: 60314.370 ms"

Код: plsql

explain analyze select count(*) from attribute_value where UPPER(string_value) like UPPER('%пись%');

Код: plaintext

1.
2.
3.
4.
5.

"Aggregate  (cost=2658902.64..2658902.65 rows=1 width=0) (actual time=34218.475..34218.475 rows=1 loops=1)"
"  ->  Seq Scan on attribute_value  (cost=0.00..2658876.92 rows=10285 width=0) (actual time=0.097..34053.918 rows=1171865 loops=1)"
"        Filter: (upper((string_value)::text) ~~ '%ПИСЬ%'::text)"
"        Rows Removed by Filter: 98126052"
"Total runtime: 34218.552 ms"

Код: plsql

explain analyze select count(*) from attribute_value where UPPER(string_value) like UPPER('%пис%') and attribute_code IN ('NAME');

Код: plaintext

1.
2.
3.
4.
5.
6.

"Aggregate  (cost=3844.76..3844.77 rows=1 width=0) (actual time=4449.045..4449.046 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on attribute_value  (cost=151.69..3842.42 rows=939 width=0) (actual time=1354.105..4364.992 rows=657892 loops=1)"
"        Recheck Cond: ((upper((string_value)::text) ~~ '%ПИС%'::text) AND ((attribute_code)::text = 'NAME'::text))"
"        ->  Bitmap Index Scan on test_pg_trgm_string_value_idx  (cost=0.00..151.46 rows=939 width=0) (actual time=1180.696..1180.696 rows=660266 loops=1)"
"              Index Cond: (upper((string_value)::text) ~~ '%ПИС%'::text)"
"Total runtime: 4450.540 ms"

Код: plsql

explain analyze select count(*) from attribute_value where UPPER(string_value) like UPPER('%пись%') and attribute_code IN ('NAME');

Код: plaintext

1.
2.
3.
4.
5.
6.
7.

"Aggregate  (cost=1214.30..1214.31 rows=1 width=0) (actual time=4087.474..4087.474 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on attribute_value  (cost=50.69..1213.57 rows=293 width=0) (actual time=1561.460..4022.354 rows=583112 loops=1)"
"        Recheck Cond: ((upper((string_value)::text) ~~ '%ПИСЬ%'::text) AND ((attribute_code)::text = 'NAME'::text))"
"        Rows Removed by Index Recheck: 10"
"        ->  Bitmap Index Scan on test_pg_trgm_string_value_idx  (cost=0.00..50.61 rows=293 width=0) (actual time=1404.071..1404.071 rows=585265 loops=1)"
"              Index Cond: (upper((string_value)::text) ~~ '%ПИСЬ%'::text)"
"Total runtime: 4088.617 ms"

...

Рейтинг:

0 / 0

27.11.2014, 16:26:13

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818572

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

its_me,

сложно сказать
но основная проблема в том что база ожидает 10000 строк отUPPER(string_value) like UPPER('%пись%')
а получает миллион строк...
какой уж тут эффективный план угадать.

без переписывания запросы вы эту задачу не решите.

...

Рейтинг:

0 / 0

27.11.2014, 16:50:29

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818586

its_me

Гость

можно ли создать другой индекс, чтобы на него клюнул, например типа:

Код: plsql

1.
2.
3.
4.
5.

CREATE INDEX test_pg_trgm_card_id_string_value_idx
  ON attribute_value
  USING gist
  (card_id, upper(string_value::text) gist_trgm_ops)
  WHERE attribute_code = 'NAME';

но такой выдает ошибку...

...

Рейтинг:

0 / 0

27.11.2014, 16:59:39

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818600

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

its_me,

для такого индекса надо btree_gin contrib доставить (и то без гарантий)

...

Рейтинг:

0 / 0

27.11.2014, 17:09:19

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818602

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

Maxim Boguk,

тьфу тоесть btree_gist конечно в вашем случае

...

Рейтинг:

0 / 0

27.11.2014, 17:09:51

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818609

its_me

Гость

Я не общался с ним, что надо сделать чтобы попробовать?

...

Рейтинг:

0 / 0

27.11.2014, 17:15:47

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818616

кхм

Гость

its_me,

Код: sql

SELECT count(DISTINCT c.card_id)

дистинкт тут какбе говорит нам, что все джойны играют роль исключительно условия проверки существования, т.е. вместо EXISTS( .... LIMIT 1) вы поднимаете все записи по джойну, а потом от них долго и упорно дистинкститесь.

За это надо руки из опы рвать с корнем.
я так думаю.

но если вы не можете переписать запрос -- снесите этот индекс ,или поставьте btree_gist и сделайте таки составной в триграмом.

...

Рейтинг:

0 / 0

27.11.2014, 17:18:55

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818620

кхм

Гость

its_me,
надо сказать

Код: sql

CREATE EXTENSION [IF NOT EXISTS] btree_gist;

...

Рейтинг:

0 / 0

27.11.2014, 17:20:33

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818650

its_me

Гость

кхмits_me,
надо сказать

Код: sql

CREATE EXTENSION [IF NOT EXISTS] btree_gist;

Это ясно, а как создать индекс и как он работать будет(как мой "нерабочий")?

...

Рейтинг:

0 / 0

27.11.2014, 17:35:40

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38818684

кхм

Гость

its_me,

создайте как хотели выше. btree_gist позволяет в т.ч. микс на уровне составного индекса.
http://www.postgresql.org/docs/9.3/static/btree-gist.html

а как и будет ли "работать" -- проверите, расскажете.

я бы, наверное, переписал на exists (SELECT 1 ...) -- гарантировал бы nested loop
по нужным индексам ORDER BY- ем LIMIT 1; Но не факт.

в кач-ве фантастики -- при вводе атрибутов запрещайте вхождение слишком частых триграммов. пусть пользователи придумывают сильно уникальные имена

...

Рейтинг:

0 / 0

27.11.2014, 17:53:36

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38819588

its_me

Гость

Создал вместо индекса test_pg_trgm_string_value_idx, индекс:

Код: plsql

1.
2.
3.
4.
5.

CREATE INDEX test_pg_trgm_card_id_string_value_idx
  ON attribute_value
  USING gist
  (card_id, upper(string_value::text) gist_trgm_ops)
  WHERE attribute_code = 'NAME';

Для %пись% результат:

Код: 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.

"Aggregate  (cost=1498.56..1498.57 rows=1 width=7) (actual time=9615.648..9615.648 rows=1 loops=1)"
"  ->  Nested Loop Left Join  (cost=63.87..1498.56 rows=1 width=7) (actual time=8180.517..9613.937 rows=1 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))))"
"        ->  Nested Loop  (cost=63.30..1405.54 rows=1 width=34) (actual time=8180.511..9613.930 rows=1 loops=1)"
"              ->  Nested Loop  (cost=63.02..1404.44 rows=1 width=17) (actual time=8180.479..9613.893 rows=1 loops=1)"
"                    ->  Bitmap Heap Scan on attribute_value av99  (cost=62.59..1209.75 rows=23 width=6) (actual time=1618.348..4972.368 rows=583654 loops=1)"
"                          Recheck Cond: ((upper((string_value)::text) ~~ '%ПИСЬ%'::text) AND ((attribute_code)::text = 'NAME'::text))"
"                          Rows Removed by Index Recheck: 10"
"                          Filter: (string_value IS NOT NULL)"
"                          ->  Bitmap Index Scan on test_pg_trgm_card_id_string_value_idx  (cost=0.00..62.58 rows=289 width=0) (actual time=1463.241..1463.241 rows=583686 loops=1)"
"                                Index Cond: (upper((string_value)::text) ~~ '%ПИСЬ%'::text)"
"                    ->  Index Scan using test_card2_id_idx on card c  (cost=0.43..8.46 rows=1 width=17) (actual time=0.007..0.007 rows=0 loops=583654)"
"                          Index Cond: (card_id = av99.card_id)"
"                          Filter: ((status_id = ANY ('{4,7}'::numeric[])) AND (template_id = 2300::numeric))"
"                          Rows Removed by Filter: 1"
"              ->  Index Scan using card_access_idx_tmpl_objid on card_access ca  (cost=0.29..1.08 rows=1 width=32) (actual time=0.024..0.028 rows=1 loops=1)"
"                    Index Cond: ((object_id = c.status_id) AND (template_id = 2300::numeric))"
"                    Filter: (permission_type = 2::numeric)"
"                    Rows Removed by Filter: 3"
"        ->  Index Scan using xif6attribute_value on attribute_value avusr  (cost=0.57..16.30 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=1)"
"              Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
"              Filter: (number_value = 7820::numeric)"
"        SubPlan 1"
"          ->  Nested Loop Left Join  (cost=0.70..76.71 rows=1 width=0) (never executed)"
"                Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.76 rows=1 width=6) (never executed)"
"                      Index Cond: (person_id = 7820::numeric)"
"                      Filter: ((role_code)::text = (ca.role_code)::text)"
"                ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..63.55 rows=32 width=12) (never executed)"
"                      Index Cond: (pr.prole_id = prole_id)"
"Total runtime: 9615.796 ms"

для %пис%:

Код: 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.

"Aggregate  (cost=2742.41..2742.42 rows=1 width=7) (actual time=60.079..60.079 rows=1 loops=1)"
"  ->  Nested Loop Left Join  (cost=14.44..2742.41 rows=1 width=7) (actual time=4.584..60.021 rows=6 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))))"
"        ->  Nested Loop  (cost=13.88..2649.39 rows=1 width=34) (actual time=4.578..60.000 rows=6 loops=1)"
"              ->  Nested Loop  (cost=13.46..950.94 rows=201 width=34) (actual time=0.115..0.708 rows=214 loops=1)"
"                    ->  Bitmap Heap Scan on card_access ca  (cost=4.45..43.07 rows=16 width=32) (actual time=0.022..0.027 rows=29 loops=1)"
"                          Recheck Cond: ((permission_type = 2::numeric) AND (template_id = 2300::numeric))"
"                          ->  Bitmap Index Scan on card_access_idx_tmpl_perm_objid  (cost=0.00..4.45 rows=16 width=0) (actual time=0.015..0.015 rows=29 loops=1)"
"                                Index Cond: ((permission_type = 2::numeric) AND (template_id = 2300::numeric))"
"                    ->  Bitmap Heap Scan on card c  (cost=9.01..56.62 rows=12 width=17) (actual time=0.014..0.019 rows=7 loops=29)"
"                          Recheck Cond: ((template_id = 2300::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
"                          ->  Bitmap Index Scan on template_and_status_idx  (cost=0.00..9.01 rows=12 width=0) (actual time=0.008..0.008 rows=7 loops=29)"
"                                Index Cond: ((template_id = 2300::numeric) AND (status_id = ca.object_id) AND (status_id = ANY ('{4,7}'::numeric[])))"
"              ->  Index Scan using test_pg_trgm_card_id_string_value_idx on attribute_value av99  (cost=0.41..8.44 rows=1 width=6) (actual time=0.272..0.276 rows=0 loops=214)"
"                    Index Cond: ((card_id = c.card_id) AND (upper((string_value)::text) ~~ '%ПИС%'::text))"
"                    Filter: (string_value IS NOT NULL)"
"        ->  Index Scan using xif6attribute_value on attribute_value avusr  (cost=0.57..16.30 rows=1 width=21) (actual time=0.001..0.001 rows=0 loops=6)"
"              Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
"              Filter: (number_value = 7820::numeric)"
"        SubPlan 1"
"          ->  Nested Loop Left Join  (cost=0.70..76.71 rows=1 width=0) (never executed)"
"                Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.76 rows=1 width=6) (never executed)"
"                      Index Cond: (person_id = 7820::numeric)"
"                      Filter: ((role_code)::text = (ca.role_code)::text)"
"                ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..63.55 rows=32 width=12) (never executed)"
"                      Index Cond: (pr.prole_id = prole_id)"
"Total runtime: 60.307 ms"

...

Рейтинг:

0 / 0

28.11.2014, 14:11:25

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38819649

этта

Гость

its_me,

у вас очень оптимистичные ожидания [, не подтверждаемые в части actual], по количеству записей в av99.
Т.ч. проще снести триграм нафик.

PS покрутите таки тупой подсчет каунтов до размножения джойнами (по exists)

сто-то на тему (надеюсь не наврал на предмет формальной эквивалентности):

Код: 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.

DEALLOCATE ALL;
PREPARE FOO (text)
AS
SELECT count( c.card_id) 
FROM  card c 
WHERE 
	EXISTS (SELECT 1 
		FROM	attribute_value av99
		WHERE 
			-- INNER ON 99
			( 
			(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($1) 
			)
		--ORDER BY card_id,....
		LIMIT 1
		)
	AND 
	EXISTS (SELECT 1 FROM		
			card_access as ca
		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 = 7820  
			)
		WHERE 
			-- INNER ON ca
			( 
			ca.object_id = c.status_id AND 
			ca.template_id = c.template_id AND 
			ca.permission_type in (2) 
			) 
		 
			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 = 7820  
					) -- OR EXISTS  
				) --   
					AND (  
					ca.person_attribute_code IS NULL  
					OR avUsr.attribute_code is not NULL  
					) -- AND  
				) -- OR  
			) -- AND 
		--ORDER BY ????? -- регулируем индекс, вдоль которого ищем сущ-е
		LIMIT 1
		) 
	AND c.template_id = (2300) 
	AND c.status_id IN (4,7)

;
----------------
explain analyze execute foo('%пись%');
----------------
explain analyze execute foo('%пис%');

...

Рейтинг:

0 / 0

28.11.2014, 14:49:15

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38820810

its_me

Гость

Код: plsql

explain analyze execute foo('%пись%');

Результат:

Код: 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.

"Aggregate  (cost=51947.57..51947.58 rows=1 width=6) (actual time=0.970..0.970 rows=1 loops=1)"
"  ->  Index Scan using template_and_status_idx on card c  (cost=0.43..51947.28 rows=115 width=6) (actual time=0.855..0.968 rows=1 loops=1)"
"        Index Cond: ((template_id = 2300::numeric) AND (status_id = ANY ('{4,7}'::numeric[])))"
"        Filter: ((SubPlan 1) AND (SubPlan 3))"
"        Rows Removed by Filter: 108"
"        SubPlan 1"
"          ->  Limit  (cost=0.68..12.57 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=109)"
"                ->  Index Scan using attribute_value_name_string_value_idx on attribute_value av99  (cost=0.68..12.57 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=109)"
"                      Index Cond: ((card_id = c.card_id) AND (string_value IS NOT NULL))"
"                      Filter: (upper((string_value)::text) ~~ '%ПИСЬ%'::text)"
"                      Rows Removed by Filter: 1"
"        SubPlan 3"
"          ->  Limit  (cost=0.85..96.85 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=1)"
"                ->  Nested Loop Left Join  (cost=0.85..96.85 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=1)"
"                      Filter: (((ca.role_code IS NULL) AND (ca.person_attribute_code IS NULL)) OR (((ca.role_code IS NULL) OR (SubPlan 2)) AND ((ca.person_attribute_code IS NULL) OR (avusr.attribute_code IS NOT NULL))))"
"                      ->  Index Scan using card_access_idx_tmpl_perm_objid on card_access ca  (cost=0.29..8.31 rows=1 width=27) (actual time=0.012..0.012 rows=1 loops=1)"
"                            Index Cond: ((permission_type = 2::numeric) AND (template_id = c.template_id) AND (object_id = c.status_id))"
"                      ->  Index Scan using xif6attribute_value on attribute_value avusr  (cost=0.57..12.45 rows=1 width=15) (actual time=0.000..0.000 rows=0 loops=1)"
"                            Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
"                            Filter: (number_value = 7820::numeric)"
"                      SubPlan 2"
"                        ->  Nested Loop Left Join  (cost=0.70..76.09 rows=1 width=0) (never executed)"
"                              Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                              ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.77 rows=1 width=6) (never executed)"
"                                    Index Cond: (person_id = 7820::numeric)"
"                                    Filter: ((role_code)::text = (ca.role_code)::text)"
"                              ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..62.92 rows=32 width=12) (never executed)"
"                                    Index Cond: (pr.prole_id = prole_id)"
"Total runtime: 1.049 ms"

Код: plsql

explain analyze execute foo('%пис%');

Результат:

Код: 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.

"Aggregate  (cost=51947.57..51947.58 rows=1 width=6) (actual time=1.006..1.006 rows=1 loops=1)"
"  ->  Index Scan using template_and_status_idx on card c  (cost=0.43..51947.28 rows=115 width=6) (actual time=0.161..1.001 rows=6 loops=1)"
"        Index Cond: ((template_id = 2300::numeric) AND (status_id = ANY ('{4,7}'::numeric[])))"
"        Filter: ((SubPlan 1) AND (SubPlan 3))"
"        Rows Removed by Filter: 103"
"        SubPlan 1"
"          ->  Limit  (cost=0.68..12.57 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=109)"
"                ->  Index Scan using attribute_value_name_string_value_idx on attribute_value av99  (cost=0.68..12.57 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=109)"
"                      Index Cond: ((card_id = c.card_id) AND (string_value IS NOT NULL))"
"                      Filter: (upper((string_value)::text) ~~ '%ПИС%'::text)"
"                      Rows Removed by Filter: 1"
"        SubPlan 3"
"          ->  Limit  (cost=0.85..96.85 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=6)"
"                ->  Nested Loop Left Join  (cost=0.85..96.85 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=6)"
"                      Filter: (((ca.role_code IS NULL) AND (ca.person_attribute_code IS NULL)) OR (((ca.role_code IS NULL) OR (SubPlan 2)) AND ((ca.person_attribute_code IS NULL) OR (avusr.attribute_code IS NOT NULL))))"
"                      ->  Index Scan using card_access_idx_tmpl_perm_objid on card_access ca  (cost=0.29..8.31 rows=1 width=27) (actual time=0.006..0.006 rows=1 loops=6)"
"                            Index Cond: ((permission_type = 2::numeric) AND (template_id = c.template_id) AND (object_id = c.status_id))"
"                      ->  Index Scan using xif6attribute_value on attribute_value avusr  (cost=0.57..12.45 rows=1 width=15) (actual time=0.000..0.000 rows=0 loops=6)"
"                            Index Cond: ((card_id = c.card_id) AND ((attribute_code)::text = (ca.person_attribute_code)::text))"
"                            Filter: (number_value = 7820::numeric)"
"                      SubPlan 2"
"                        ->  Nested Loop Left Join  (cost=0.70..76.09 rows=1 width=0) (never executed)"
"                              Filter: (COALESCE(prt.template_id, ca.template_id) = ca.template_id)"
"                              ->  Index Scan using xif1person_role on person_role pr  (cost=0.28..12.77 rows=1 width=6) (never executed)"
"                                    Index Cond: (person_id = 7820::numeric)"
"                                    Filter: ((role_code)::text = (ca.role_code)::text)"
"                              ->  Index Scan using xif2person_role_template on person_role_template prt  (cost=0.42..62.92 rows=32 width=12) (never executed)"
"                                    Index Cond: (pr.prole_id = prole_id)"
"Total runtime: 1.083 ms"

...

Рейтинг:

0 / 0

30.11.2014, 19:18:05

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38823314

its_me

Гость

...

Рейтинг:

0 / 0

03.12.2014, 10:39:04

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38826070

its_me

Гость

...

Рейтинг:

0 / 0

05.12.2014, 12:33:22

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38826135

кхм

Гость

its_me,

а чего вы хотите?

вам же сразу написали :

автортут явно неудачно оценивается в первом запросе селективность триграмного индекса
и это никуда не делось.

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

у меня вот случай, когда

Код: sql

SELECT count() FROM .... WHERE f1>$1 AND f1<$2

"вдоль" индекса f1_idx (в плане)
считается в разы быстрее чем

Код: sql

SELECT count() FROM .... WHERE f1>$1 AND f1<$2 AND f2 LIKE $3

"вдоль" индекса f2_f1_idx -- при достаточно селективном, на мой вкус f2 LIKE $3.

-- что довольно контринтуитивно

при этом можно считать что
1. таблица практически кластеризована по f1_idx
2. вероятно изрядная его [f1_idx] часть все время в кеше

...

Рейтинг:

0 / 0

05.12.2014, 13:12:34

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38826259

its_me

Гость

Запросы не изменить. Я так понял надо смириться.

...

Рейтинг:

0 / 0

05.12.2014, 14:19:57

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38827610

UKY

Гость

Сам никогда не пробовал, но, возможно, вам подойдёт костыль под названием RULE:
CREATE RULE

...

Рейтинг:

0 / 0

08.12.2014, 05:11:38

| Ответить | Цитировать | Написать

Как оптимизировать время выполнения скрипта?

#38827737

its_me

Гость

Как его применить если имеются и другие аналогичные запросы для которых не надо применять это правило? Есть запросы содержащие

Код: plsql

1.
2.
3.
4.
5.
6.

                 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('%пись%') 
		 )

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

...

Рейтинг:

0 / 0

08.12.2014, 10:30:18

| Ответить | Цитировать | Написать

25 сообщений из 31, страница 1 из 2

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как оптимизировать время выполнения скрипта?

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=38819588&tid=1998301]:	0ms
get settings:	7ms
get forum list:	18ms
check forum access:	3ms
check topic access:	3ms
track hit:	177ms
get topic data:	9ms
get forum data:	2ms
get page messages:	56ms
get tp. blocked users:	1ms
others:	206ms

total:	482ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы