Оптимизация большого запроса возможна? / PostgreSQL

ReSQL.ru

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

Гость

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

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

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

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

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

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

все

Оптимизация большого запроса возможна?

#35453976

UKY

Гость

Почему-то не получется так сделать:

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

create or replace function find_all(pattern varchar) returns record as
$$
DECLARE
	rec record;
begin
	create or replace temp view find_tths AS
	select	distinct on (tths.tth)
		tths.id AS tth_id
	from	files join tths on files.tth = tths.id
	where	files.size >  0  and
		files.type is not null and
		to_tsvector('russian', files.name) @@ to_tsquery(pattern);

	FOR rec IN
	select 	distinct on (count, tths.id, files.name)
		servers.ip	AS ip,
		services.nick	AS nick,
		folders.path	AS path,
		ts_headline('russian', files.name || '.' || files.type, to_tsquery(pattern))
				AS summary,
		files.size	AS size,
		tths.added	AS added,
		tths.tth	AS tth,
		tths.id		AS id,
		tths.count	AS count
	from	servers
		join services	on (servers.id = services.server)
		join folders	on (folders.service = services.id)
		join files	on (files.path = folders.id)
		join tths	on (files.tth = tths.id)
		join
		(
	--explain analyze
			select * from find_tths
		) AS inq
		on (tths.id = inq.tth_id)
	UNION ALL
	--explain analyze
	select 	servers.ip,
		services.nick,
		ts_headline('russian', folders.path, to_tsquery(pattern)),
		NULL,
		files.size,
		NULL,
		NULL,
		NULL AS id,
		NULL AS count
	from	servers
		join services	on (servers.id = services.server)
		join folders	on (folders.service = services.id)
		join files	on (files.path = folders.id)
	where
		files.type is null and
		files.tth is null and
		to_tsvector('russian', files.name) @@ to_tsquery(pattern)
	order 	by count desc NULLS LAST, id
	LOOP
		return rec;
	END LOOP;
end;
$$
language plpgsql;

Когда вызываю

Код: plaintext

select * from find_all('панда') AS (ip inet, nick varchar, path varchar, summary varchar, size bigint, added date, tth varchar, id integer, count integer)

Пишет вот такую ошибку:
PL/pgSQL function "find_all" line 4 at SQL statement
ERROR: there is no parameter $1
CONTEXT: SQL statement "create or replace temp view find_tths AS select distinct on (tths.tth) tths.id AS tth_id from files join tths on files.tth = tths.id where files.size > 0 and files.type is not null and to_tsvector('russian', files.name) @@ to_tsquery( $1 )"

Странно это все как-то...

...

Рейтинг:

0 / 0

27.07.2008, 13:12

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

Оптимизация большого запроса возможна?

#35453991

MBG

Гость

Используйте EXECUTE. Сейчас вы пытаетесь выполнить свой запрос как уже готовый, а на самом деле вам его сначала надо подговить, обеспечив подстановку переменных.

...

Рейтинг:

0 / 0

27.07.2008, 13:25

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

Оптимизация большого запроса возможна?

#35453998

UKY

Гость

Сейчас попробовал сделать в функции без параметров и опять начало тормозить.
Не одно так другое тормозит :)

...

Рейтинг:

0 / 0

27.07.2008, 13:29

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

Оптимизация большого запроса возможна?

#35454002

UKY

Гость

MBGИспользуйте EXECUTE. Сейчас вы пытаетесь выполнить свой запрос как уже готовый, а на самом деле вам его сначала надо подговить, обеспечив подстановку переменных.
Ой, точно! Что-то я уже тупить начинаю %) Видимо, нужно развеяться :)

Но из функции выборка делается дольше. Это факт. Видимо, из-за того, что она использует курсор в цикле.

...

Рейтинг:

0 / 0

27.07.2008, 13:37

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

Оптимизация большого запроса возможна?

#35454009

MBG

Гость

А зачем вам там курсор? В функции надо создать таблицу, а потом ее прочитать после выхода из функции. Не надо пытаться вернуть таблицу из функции, в постгресе такая операция весьма медленная, не путайте с ораклом.

...

Рейтинг:

0 / 0

27.07.2008, 13:41

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

Оптимизация большого запроса возможна?

#35454016

UKY

Гость

Нет, к сожалению, я ошибся. Все намного хуже.
Сейчас сделал так, как было раньше без функции. Опять тормозит. Так что функция здесь не при чем.

Посмотрел план: опять производится seqscan по files. План поменялся.

...

Рейтинг:

0 / 0

27.07.2008, 13:46

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

Оптимизация большого запроса возможна?

#35454019

MBG

Гость

Обычное дело. Это, собственно, одна из причин, по которой я сам отказался от постгреса. Оптимизировать запрос можно, но изменится количество записей в одной из больших таблиц на порядок и снова несколько дней проведете в переборе вариантов объединений таблиц и создании временных таблиц/видов. А вообще ваш запрос довольно простой, до 10 таблиц оптимизировать можно. Попробуйте переписать запрос, имхо он написан не оптимально.

...

Рейтинг:

0 / 0

27.07.2008, 13:57

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

Оптимизация большого запроса возможна?

#35454029

MBG

Гость

Вы, случаем, не на рабочей системе тестируете? Планировщик постгреса так устроен, что подстраивается во время работы, то есть если у вас на системе выполнено много одинаковых запросов план будет отличаться от плана первого выполнения запроса.

...

Рейтинг:

0 / 0

27.07.2008, 14:14

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

Оптимизация большого запроса возможна?

#35454055

UKY

Гость

Нет. Я работаю на статичной базе. Тестирую, экспериментирую, пробую :)

...

Рейтинг:

0 / 0

27.07.2008, 15:34

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

Оптимизация большого запроса возможна?

#35454149

KRED

Участник

Откуда: München/Augsburg (Germany)

Сообщения: 611

Рейтинг: 0 / 0

Может стоить попробовать условие "text like '%панда%'" разбить на два "text like 'панда%' OR text like '%панда'" ну и по полю построить два индекса - один обычный , а второй со спецальными опциями (поищи на форуме) для "обратного %" "%панда" ?

...

Рейтинг:

0 / 0

27.07.2008, 19:13

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

Оптимизация большого запроса возможна?

#35454250

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

Забыл.

Код: plaintext

effective_cache_size = 1G #

This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used.

Т.е. если ваши индексы не лезут в предполагаемый размер дискового кеша, то запрос сорвется в seq_scan

И еще попробуйте:

Код: plaintext

geqo_threshold =  32  # а может и больше..
from_collapse_limit = 16

Это заставит планировщик даже для сложных запросов применять аналитический метод. Т.е. планировщик будет искать действительно оптимальный план детерминистским методом, а не переключаться на эвристику.

...

Рейтинг:

0 / 0

27.07.2008, 22:47

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

Оптимизация большого запроса возможна?

#35454349

UKY

Гость

Неа. Не помогает. Пока что рулит только set enable_seqscan to on :)

...

Рейтинг:

0 / 0

28.07.2008, 05:49

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

Оптимизация большого запроса возможна?

#35454350

UKY

Гость

UKYНеа. Не помогает. Пока что рулит только set enable_seqscan to on :)
Точнее off ;)

...

Рейтинг:

0 / 0

28.07.2008, 05:49

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

Оптимизация большого запроса возможна?

#35454564

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

Если и по колесу стучали и зажигание проверяли (перезапускали после каждого изменения конфига и делали vacuum analyze), тогда надо играть с комбинацией из нескольких view.

Далее советы очень трудно давать, не видя базы. Чужие explain analyze не так наглядны, заочно только великие адепты могут помочь.

...

Рейтинг:

0 / 0

28.07.2008, 10:10

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

Оптимизация большого запроса возможна?

#35455058

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKY

Код: plaintext

1.
2.
3.
4.
5.

->  Hash Join  (cost= 90 . 54 .. 280244 . 78  rows= 2080  width= 334 ) (actual time= 644 . 000 .. 57193 . 141  rows= 130  loops= 1 )
    Hash Cond: (public.files.tth = tths.id)
    ->  Seq Scan on files  (cost= 0 . 00 .. 260301 . 67  rows= 5293967  width= 286 ) (actual time= 0 . 007 .. 44122 . 366  rows= 5293967  loops= 1 )
    ->  Hash  (cost= 90 . 41 .. 90 . 41  rows= 10  width= 56 ) (actual time= 3 . 629 .. 3 . 629  rows= 24  loops= 1 )

CREATE INDEX files_tth_index ON files USING btree (tth) WHERE (type IS NULL)

в первом подзапросе (от SELECT до UNION) нет условия WHERE files.type IS NULL. поэтому уберите это условие из индекса по files(tth). покажите получившийся EXPLAIN ANALYZE.

...

Рейтинг:

0 / 0

28.07.2008, 13:00

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

Оптимизация большого запроса возможна?

#35455125

Author the new one

Участник

Сообщения: 300

Рейтинг: 0 / 0

UKYДоброго времени суток, многоуважаемые :)

Помогите, пожалуйста, оптимизировать запрос:

Я бы начал с того, что убедился, что существуют индексы:

Код: plaintext

1.
2.
3.

tths(id)
files(tth,path)
folders(id, sevice)
services(id)

Кроме того, в позапросе во from (который select distinct on (tths.tth)...) от греха подальше поставил бы limit 10, скажем.

...

Рейтинг:

0 / 0

28.07.2008, 13:16

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

Оптимизация большого запроса возможна?

#35457033

UKY

Гость

LeXa NalBat UKY

Код: plaintext

CREATE INDEX files_tth_index ON files USING btree (tth) WHERE (type IS NULL)

в первом подзапросе (от SELECT до UNION) нет условия WHERE files.type IS NULL. поэтому уберите это условие из индекса по files(tth). покажите получившийся EXPLAIN ANALYZE.
Убрал условие.

Сделал индекс

Код: plaintext

CREATE INDEX files_name_tsvector_index	ON files USING gin(to_tsvector('russian', name || coalesce('.' || type, '')))
									TABLESPACE indexspace;

Сделал VACUUM FULL ANALYZE.

Немного переделал запрос. Получился вот такой:

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

select 	distinct on (count, tth_id, name)
	service.prefix	AS prefix,
	services.hub	AS hub,
	servers.ip	AS ip,
	services.nick	AS nick,
	folders.path	AS path,
	files.name	AS name,
	files.type	AS type,
	files.size	AS size,
	tths.count	AS count,
	tths.id		AS tth_id
	--ts_headline('russian', files.name || '.' || files.type, to_tsquery(pattern))
from	service
	join services	on (service.id = services.type)
	join servers	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
	join tths	on (files.tth = tths.id)
	join
	(
--explain analyze
		select	distinct on (tths.id)
			tths.id AS tth_id
		from 	files join tths on (files.tth = tths.id)
		where	to_tsvector('russian', files.name || coalesce('.' || files.type, '')) @@ to_tsquery('панда')
	) AS inq
	on (tths.id = inq.tth_id)
order 	by count desc, tth_id, 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.
32.
33.
34.
35.
36.
37.
38.

Unique  (cost= 723161 . 22 .. 733706 . 45  rows= 1054523  width= 378 ) (actual time= 67955 . 121 .. 67955 . 390  rows= 29  loops= 1 )
  ->  Sort  (cost= 723161 . 22 .. 725797 . 52  rows= 1054523  width= 378 ) (actual time= 67955 . 115 .. 67955 . 214  rows= 59  loops= 1 )
        Sort Key: public.tths.count, public.tths.id, public.files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Hash Join  (cost= 109233 . 81 .. 433842 . 40  rows= 1054523  width= 378 ) (actual time= 8333 . 997 .. 67954 . 163  rows= 59  loops= 1 )
              Hash Cond: (public.files.path = folders.id)
              ->  Hash Join  (cost= 92216 . 48 .. 397052 . 76  rows= 1054523  width= 291 ) (actual time= 4264 . 654 .. 63884 . 350  rows= 59  loops= 1 )
                    Hash Cond: (public.files.tth = public.tths.id)
                    ->  Seq Scan on files  (cost= 0 . 00 .. 258449 . 20  rows= 5290120  width= 287 ) (actual time= 0 . 046 .. 50781 . 539  rows= 5302841  loops= 1 )
                    ->  Hash  (cost= 92154 . 48 .. 92154 . 48  rows= 4960  width= 12 ) (actual time= 2 . 047 .. 2 . 047  rows= 19  loops= 1 )
                          ->  Nested Loop  (cost= 56516 . 24 .. 92154 . 48  rows= 4960  width= 12 ) (actual time= 1 . 429 .. 2 . 001  rows= 19  loops= 1 )
                                ->  Unique  (cost= 56516 . 24 .. 56541 . 04  rows= 4960  width= 4 ) (actual time= 1 . 412 .. 1 . 605  rows= 19  loops= 1 )
                                      ->  Sort  (cost= 56516 . 24 .. 56528 . 64  rows= 4960  width= 4 ) (actual time= 1 . 407 .. 1 . 486  rows= 47  loops= 1 )
                                            Sort Key: public.tths.id
                                            Sort Method:  quicksort  Memory: 27kB
                                            ->  Nested Loop  (cost= 178 . 60 .. 56211 . 79  rows= 4960  width= 4 ) (actual time= 0 . 152 .. 1 . 283  rows= 47  loops= 1 )
                                                  ->  Bitmap Heap Scan on files  (cost= 178 . 60 .. 18705 . 13  rows= 5290  width= 4 ) (actual time= 0 . 120 .. 0 . 243  rows= 49  loops= 1 )
                                                        Recheck Cond: (to_tsvector('russian'::regconfig, ((name)::text || COALESCE(('.'::text || (type)::text), ''::text))) @@ to_tsquery('панда'::text))
                                                        ->  Bitmap Index Scan on files_name_tsvector_index  (cost= 0 . 00 .. 177 . 28  rows= 5290  width= 0 ) (actual time= 0 . 107 .. 0 . 107  rows= 49  loops= 1 )
                                                              Index Cond: (to_tsvector('russian'::regconfig, ((name)::text || COALESCE(('.'::text || (type)::text), ''::text))) @@ to_tsquery('панда'::text))
                                                  ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 7 . 08  rows= 1  width= 4 ) (actual time= 0 . 012 .. 0 . 014  rows= 1  loops= 49 )
                                                        Index Cond: (public.tths.id = public.files.tth)
                                ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 7 . 16  rows= 1  width= 8 ) (actual time= 0 . 007 .. 0 . 009  rows= 1  loops= 19 )
                                      Index Cond: (public.tths.id = public.tths.id)
              ->  Hash  (cost= 12730 . 06 .. 12730 . 06  rows= 342982  width= 99 ) (actual time= 4069 . 138 .. 4069 . 138  rows= 343308  loops= 1 )
                    ->  Hash Join  (cost= 39 . 24 .. 12730 . 06  rows= 342982  width= 99 ) (actual time= 8 . 425 .. 2901 . 313  rows= 343308  loops= 1 )
                          Hash Cond: (folders.service = services.id)
                          ->  Seq Scan on folders  (cost= 0 . 00 .. 7974 . 82  rows= 342982  width= 76 ) (actual time= 0 . 020 .. 875 . 335  rows= 343308  loops= 1 )
                          ->  Hash  (cost= 34 . 06 .. 34 . 06  rows= 414  width= 31 ) (actual time= 8 . 382 .. 8 . 382  rows= 414  loops= 1 )
                                ->  Hash Join  (cost= 13 . 04 .. 34 . 06  rows= 414  width= 31 ) (actual time= 1 . 997 .. 7 . 182  rows= 414  loops= 1 )
                                      Hash Cond: (services.server = servers.id)
                                      ->  Nested Loop  (cost= 0 . 00 .. 15 . 33  rows= 414  width= 28 ) (actual time= 0 . 032 .. 3 . 038  rows= 414  loops= 1 )
                                            Join Filter: (service.id = services.type)
                                            ->  Seq Scan on service  (cost= 0 . 00 .. 1 . 01  rows= 1  width= 13 ) (actual time= 0 . 005 .. 0 . 007  rows= 1  loops= 1 )
                                            ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 14  rows= 414  width= 23 ) (actual time= 0 . 012 .. 0 . 864  rows= 414  loops= 1 )
                                      ->  Hash  (cost= 8 . 02 .. 8 . 02  rows= 402  width= 11 ) (actual time= 1 . 946 .. 1 . 946  rows= 402  loops= 1 )
                                            ->  Seq Scan on servers  (cost= 0 . 00 .. 8 . 02  rows= 402  width= 11 ) (actual time= 0 . 013 .. 0 . 922  rows= 402  loops= 1 )
Total runtime:  67955 . 752  ms

Делал вид по

Код: plaintext

1.
2.
3.
4.

select	distinct on (tths.id)
	tths.id AS tth_id
from 	files join tths on (files.tth = tths.id)
where	to_tsvector('russian', files.name || coalesce('.' || files.type, '')) @@ to_tsquery('панда')

Тоже самое.

И возник еще один вопрос:
Почему не используется индекс

Код: plaintext

CREATE INDEX tths_count_index		ON tths(count DESC NULLS LAST)	TABLESPACE indexspace;

для сортировки результатов выдачи? (даже если пишу order by count desc nulls last)

...

Рейтинг:

0 / 0

29.07.2008, 10:25

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

Оптимизация большого запроса возможна?

#35457204

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKY LeXa NalBatв первом подзапросе (от SELECT до UNION) нет условия WHERE files.type IS NULL. поэтому уберите это условие из индекса по files(tth). покажите получившийся EXPLAIN ANALYZE.Убрал условие.

Немного переделал запрос.не так. пробуйте запрос через функцию tth_search при наличии индекса без дополнительных условий по files(tth)

UKYИ возник еще один вопрос:
Почему не используется индекс tths(count DESC NULLS LAST) для сортировки результатов выдачи? (даже если пишу order by count desc nulls last)наверное потому, что индекс только по одному полю count, а order by по трем полям count desc, tth_id, name. но это не важно, потому что 59 строк сортируются быстро.

...

Рейтинг:

0 / 0

29.07.2008, 11:17

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

Оптимизация большого запроса возможна?

#35457409

UKY

Гость

LeXa NalBat
не так. пробуйте запрос через функцию tth_search при наличии индекса без дополнительных условий по files(tth)

Да! Получилось! :)

Вот окончательный результат:

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

create or replace function tth_search(pattern text) returns setof integer as
$$
BEGIN
	return query
	select	distinct on (tths.id)
		tths.id
	from 	files join tths on (files.tth = tths.id)
	where	to_tsvector('russian', files.name || coalesce('.' || files.type, '')) @@ to_tsquery(pattern);
END;
$$
language plpgsql rows  20 ;

explain analyze
select 	distinct on (count, tth_id, name)
	service.prefix	AS prefix,
	services.hub	AS hub,
	servers.ip	AS ip,
	services.nick	AS nick,
	folders.path	AS path,
	files.name	AS name,
	files.type	AS type,
	files.size	AS size,
	tths.count	AS count,
	tths.id		AS tth_id
	--ts_headline('russian', files.name || '.' || files.type, to_tsquery(pattern))
from	service
	join services	on (service.id = services.type)
	join servers	on (servers.id = services.server)
	join folders	on (folders.service = services.id)
	join files	on (files.path = folders.id)
	join tths	on (files.tth = tths.id)
	join
	(
--explain analyze
		select tth_search from tth_search('панда')
	) AS inq
	on (tths.id = inq.tth_search)
order 	by count desc, tth_id, 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.

Unique  (cost= 28453 . 56 .. 28494 . 00  rows= 4044  width= 376 ) (actual time= 2100 . 627 .. 2100 . 892  rows= 29  loops= 1 )
  ->  Sort  (cost= 28453 . 56 .. 28463 . 67  rows= 4044  width= 376 ) (actual time= 2100 . 622 .. 2100 . 721  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Hash Join  (cost= 12318 . 18 .. 28211 . 29  rows= 4044  width= 376 ) (actual time= 2097 . 461 .. 2099 . 437  rows= 59  loops= 1 )
              Hash Cond: (folders.service = services.id)
              ->  Hash Join  (cost= 12278 . 95 .. 28116 . 45  rows= 4044  width= 353 ) (actual time= 2089 . 254 .. 2090 . 773  rows= 59  loops= 1 )
                    Hash Cond: (files.path = folders.id)
                    ->  Nested Loop  (cost= 0 . 00 .. 15761 . 68  rows= 4044  width= 290 ) (actual time= 0 . 970 .. 2 . 083  rows= 59  loops= 1 )
                          ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 0 . 947 .. 1 . 324  rows= 19  loops= 1 )
                                ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 0 . 925 .. 0 . 959  rows= 19  loops= 1 )
                                ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 010 .. 0 . 012  rows= 1  loops= 19 )
                                      Index Cond: (tths.id = tth_search.tth_search)
                          ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 776 . 57  rows= 216  width= 286 ) (actual time= 0 . 008 .. 0 . 017  rows= 3  loops= 19 )
                                Index Cond: (files.tth = tths.id)
                    ->  Hash  (cost= 7982 . 31 .. 7982 . 31  rows= 343731  width= 75 ) (actual time= 2088 . 048 .. 2088 . 048  rows= 343308  loops= 1 )
                          ->  Seq Scan on folders  (cost= 0 . 00 .. 7982 . 31  rows= 343731  width= 75 ) (actual time= 0 . 085 .. 1098 . 678  rows= 343308  loops= 1 )
              ->  Hash  (cost= 34 . 06 .. 34 . 06  rows= 414  width= 31 ) (actual time= 8 . 183 .. 8 . 183  rows= 414  loops= 1 )
                    ->  Hash Join  (cost= 13 . 04 .. 34 . 06  rows= 414  width= 31 ) (actual time= 2 . 201 .. 7 . 043  rows= 414  loops= 1 )
                          Hash Cond: (services.server = servers.id)
                          ->  Nested Loop  (cost= 0 . 00 .. 15 . 33  rows= 414  width= 28 ) (actual time= 0 . 038 .. 2 . 909  rows= 414  loops= 1 )
                                Join Filter: (service.id = services.type)
                                ->  Seq Scan on service  (cost= 0 . 00 .. 1 . 01  rows= 1  width= 13 ) (actual time= 0 . 011 .. 0 . 013  rows= 1  loops= 1 )
                                ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 14  rows= 414  width= 23 ) (actual time= 0 . 010 .. 0 . 885  rows= 414  loops= 1 )
                          ->  Hash  (cost= 8 . 02 .. 8 . 02  rows= 402  width= 11 ) (actual time= 2 . 142 .. 2 . 142  rows= 402  loops= 1 )
                                ->  Seq Scan on servers  (cost= 0 . 00 .. 8 . 02  rows= 402  width= 11 ) (actual time= 0 . 013 .. 1 . 132  rows= 402  loops= 1 )
Total runtime:  2101 . 217  ms

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

Спасибо еще раз :)

...

Рейтинг:

0 / 0

29.07.2008, 12:08

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

Оптимизация большого запроса возможна?

#35457443

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYДа! Получилось! :)ура!

UKY

Код: plaintext

1.
2.
3.
4.

              ->  Hash Join  (cost= 12278 . 95 .. 28116 . 45  rows= 4044  width= 353 ) (actual time= 2089 . 254 .. 2090 . 773  rows= 59  loops= 1 )
                    Hash Cond: (files.path = folders.id)
                    ->  Nested Loop  (cost= 0 . 00 .. 15761 . 68  rows= 4044  width= 290 ) (actual time= 0 . 970 .. 2 . 083  rows= 59  loops= 1 )
                    ->  Hash  (cost= 7982 . 31 .. 7982 . 31  rows= 343731  width= 75 ) (actual time= 2088 . 048 .. 2088 . 048  rows= 343308  loops= 1 )
                          ->  Seq Scan on folders  (cost= 0 . 00 .. 7982 . 31  rows= 343731  width= 75 ) (actual time= 0 . 085 .. 1098 . 678  rows= 343308  loops= 1 )

сделайте индекс по folders(id), должно заметно ускориться, покажите explain analyze.

...

Рейтинг:

0 / 0

29.07.2008, 12:18

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

Оптимизация большого запроса возможна?

#35457507

UKY

Гость

Да, я сам заметил, что все еще последовательный перебор используется. И не только по таблице папок...

Вот какие индексы есть в системе:

Код: plaintext

select indexdef from pg_indexes where indexname not like 'pg_%'

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

CREATE UNIQUE INDEX servers_pkey ON servers USING btree (id)
CREATE UNIQUE INDEX service_pkey ON service USING btree (id)
CREATE INDEX services_type_index ON services USING btree (type)
CREATE INDEX services_server_index ON services USING btree (server)
CREATE UNIQUE INDEX services_pkey ON services USING btree (id)
CREATE INDEX folders_rubruc_index ON folders USING btree (rubric)
CREATE INDEX folders_service_index ON folders USING btree (service)
CREATE UNIQUE INDEX folders_pkey ON folders USING btree (id)
CREATE UNIQUE INDEX unique_tths_tth_size ON tths USING btree (tth, size)
CREATE INDEX tths_count_index ON tths USING btree (count DESC NULLS LAST)
CREATE UNIQUE INDEX tths_tth_key ON tths USING btree (tth)
CREATE UNIQUE INDEX tths_pkey ON tths USING btree (id)
CREATE INDEX files_type_index ON files USING btree (type)
CREATE INDEX files_tth_index ON files USING btree (tth)
CREATE INDEX files_name_tsvector_index ON files USING gin (to_tsvector('russian'::regconfig, ((name)::text || COALESCE(('.'::text || (type)::text), ''::text))))
CREATE INDEX files_path_index ON files USING btree (path)
CREATE UNIQUE INDEX files_pkey ON files USING btree (id)
CREATE UNIQUE INDEX streets_pkey ON streets USING btree (id)
CREATE UNIQUE INDEX houses_pkey ON houses USING btree (id)
CREATE UNIQUE INDEX networks_pkey ON networks USING btree (id)

Так что они и здесь почему-то не используются... Это же лечится? 0:)
P.S.:
Если сделать set enable_seqscan to off то запрос выполняется за 179 ms О_о
Я таких скоростей никогда не видел :) Даже подумал нафиг новый сервер покупать и этот неплохо справляется... 0:)

...

Рейтинг:

0 / 0

29.07.2008, 12:34

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

Оптимизация большого запроса возможна?

#35457584

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYДа, я сам заметил, что все еще последовательный перебор используется. И не только по таблице папок...

Вот какие индексы есть в системе:
...
CREATE UNIQUE INDEX folders_pkey ON folders USING btree (id)
...

Так что они и здесь почему-то не используются... Это же лечится? 0:)

Если сделать set enable_seqscan to off то запрос выполняется за 179 ms О_ода, нужный индекс есть. может получиться запинать до десятков миллисекунд. покажите пожалуйста explain analyze с set enable_seqscan to off, который 179 ms.

Код: plaintext

->  Index Scan using files_tth_index on files (... rows= 216  ...) (actual ... rows= 3  ...)
    Index Cond: (files.tth = tths.id)

здесь постгрес сильно ошибается в оценке кол-ва строк, удовлетворяющих условию files.tth=$1. и наверное вследствии этого выбирает неоптимальный план.

попробуйте выполнить запрос после сбора самой подробной статистики по этой колонке:
ALTER TABLE files ALTER COLUMN tths SET STATISTICS 1000;
ANALYZE files ( tths );
EXPLAIN ANALYZE SELECT ...

...

Рейтинг:

0 / 0

29.07.2008, 12:55

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

Оптимизация большого запроса возможна?

#35457628

UKY

Гость

На кэшированных данных еще быстрее (с set enable_seqscan to off):

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

Unique  (cost= 32206 . 46 .. 32246 . 90  rows= 4044  width= 376 ) (actual time= 11 . 473 .. 11 . 828  rows= 29  loops= 1 )
  ->  Sort  (cost= 32206 . 46 .. 32216 . 57  rows= 4044  width= 376 ) (actual time= 11 . 468 .. 11 . 604  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Hash Join  (cost= 99 . 79 .. 31964 . 20  rows= 4044  width= 376 ) (actual time= 6 . 355 .. 10 . 481  rows= 59  loops= 1 )
              Hash Cond: (services.server = servers.id)
              ->  Nested Loop  (cost= 35 . 64 .. 31844 . 44  rows= 4044  width= 373 ) (actual time= 4 . 291 .. 7 . 338  rows= 59  loops= 1 )
                    Join Filter: (services.type = service.id)
                    ->  Index Scan using service_pkey on service  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 13 ) (actual time= 0 . 020 .. 0 . 023  rows= 1  loops= 1 )
                    ->  Hash Join  (cost= 35 . 64 .. 31785 . 62  rows= 4044  width= 368 ) (actual time= 4 . 259 .. 7 . 052  rows= 59  loops= 1 )
                          Hash Cond: (folders.service = services.id)
                          ->  Nested Loop  (cost= 0 . 00 .. 31694 . 38  rows= 4044  width= 353 ) (actual time= 1 . 733 .. 4 . 214  rows= 59  loops= 1 )
                                ->  Nested Loop  (cost= 0 . 00 .. 15761 . 68  rows= 4044  width= 290 ) (actual time= 1 . 704 .. 2 . 894  rows= 59  loops= 1 )
                                      ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 1 . 682 .. 2 . 153  rows= 19  loops= 1 )
                                            ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 1 . 645 .. 1 . 686  rows= 20  loops= 1 )
                                            ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 012 .. 0 . 015  rows= 1  loops= 20 )
                                                  Index Cond: (tths.id = tth_search.tth_search)
                                      ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 776 . 57  rows= 216  width= 286 ) (actual time= 0 . 012 .. 0 . 021  rows= 3  loops= 19 )
                                            Index Cond: (files.tth = tths.id)
                                ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 3 . 93  rows= 1  width= 75 ) (actual time= 0 . 012 .. 0 . 014  rows= 1  loops= 59 )
                                      Index Cond: (folders.id = files.path)
                          ->  Hash  (cost= 30 . 46 .. 30 . 46  rows= 414  width= 23 ) (actual time= 2 . 495 .. 2 . 495  rows= 414  loops= 1 )
                                ->  Index Scan using services_type_index on services  (cost= 0 . 00 .. 30 . 46  rows= 414  width= 23 ) (actual time= 0 . 025 .. 1 . 250  rows= 414  loops= 1 )
              ->  Hash  (cost= 59 . 13 .. 59 . 13  rows= 402  width= 11 ) (actual time= 2 . 039 .. 2 . 039  rows= 402  loops= 1 )
                    ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 59 . 13  rows= 402  width= 11 ) (actual time= 0 . 026 .. 1 . 043  rows= 402  loops= 1 )
Total runtime:  12 . 142  ms

Сделал статистику побольше, как Вы и советовали. Теперь план практически такой же, как и с set enable_seqscan to off:

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

Unique  (cost= 1212 . 20 .. 1213 . 37  rows= 117  width= 376 ) (actual time= 9 . 313 .. 9 . 673  rows= 29  loops= 1 )
  ->  Sort  (cost= 1212 . 20 .. 1212 . 49  rows= 117  width= 376 ) (actual time= 9 . 307 .. 9 . 420  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Hash Join  (cost= 27 . 36 .. 1208 . 18  rows= 117  width= 376 ) (actual time= 4 . 695 .. 8 . 365  rows= 59  loops= 1 )
              Hash Cond: (services.server = servers.id)
              ->  Nested Loop  (cost= 14 . 32 .. 1193 . 53  rows= 117  width= 373 ) (actual time= 2 . 795 .. 6 . 169  rows= 59  loops= 1 )
                    Join Filter: (services.type = service.id)
                    ->  Seq Scan on service  (cost= 0 . 00 .. 1 . 01  rows= 1  width= 13 ) (actual time= 0 . 004 .. 0 . 008  rows= 1  loops= 1 )
                    ->  Hash Join  (cost= 14 . 32 .. 1191 . 05  rows= 117  width= 368 ) (actual time= 2 . 781 .. 5 . 904  rows= 59  loops= 1 )
                          Hash Cond: (folders.service = services.id)
                          ->  Nested Loop  (cost= 0 . 00 .. 1175 . 13  rows= 117  width= 353 ) (actual time= 0 . 521 .. 3 . 322  rows= 59  loops= 1 )
                                ->  Nested Loop  (cost= 0 . 00 .. 714 . 17  rows= 117  width= 290 ) (actual time= 0 . 496 .. 1 . 852  rows= 59  loops= 1 )
                                      ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 0 . 474 .. 0 . 929  rows= 19  loops= 1 )
                                            ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 0 . 440 .. 0 . 483  rows= 20  loops= 1 )
                                            ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 012 .. 0 . 014  rows= 1  loops= 20 )
                                                  Index Cond: (tths.id = tth_search.tth_search)
                                      ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 26 . 82  rows= 6  width= 286 ) (actual time= 0 . 011 .. 0 . 031  rows= 3  loops= 19 )
                                            Index Cond: (files.tth = tths.id)
                                ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 3 . 93  rows= 1  width= 75 ) (actual time= 0 . 013 .. 0 . 016  rows= 1  loops= 59 )
                                      Index Cond: (folders.id = files.path)
                          ->  Hash  (cost= 9 . 14 .. 9 . 14  rows= 414  width= 23 ) (actual time= 2 . 238 .. 2 . 238  rows= 414  loops= 1 )
                                ->  Seq Scan on services  (cost= 0 . 00 .. 9 . 14  rows= 414  width= 23 ) (actual time= 0 . 013 .. 1 . 184  rows= 414  loops= 1 )
              ->  Hash  (cost= 8 . 02 .. 8 . 02  rows= 402  width= 11 ) (actual time= 1 . 877 .. 1 . 877  rows= 402  loops= 1 )
                    ->  Seq Scan on servers  (cost= 0 . 00 .. 8 . 02  rows= 402  width= 11 ) (actual time= 0 . 022 .. 0 . 926  rows= 402  loops= 1 )
Total runtime:  9 . 985  ms

Сижу теперь и офигеваю: с 70 секунд ускорить выборку до 10 мс... Офигеть.

...

Рейтинг:

0 / 0

29.07.2008, 13:07

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

Оптимизация большого запроса возможна?

#35457746

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

UKYНа кэшированных данных еще быстрее (с set enable_seqscan to off):

Код: plaintext

1.
2.
3.
4.

->  Hash  (cost= 30 . 46 .. 30 . 46  rows= 414  width= 23 ) (actual time= 2 . 495 .. 2 . 495  rows= 414  loops= 1 )
    ->  Index Scan using services_type_index on services  (cost= 0 . 00 .. 30 . 46  rows= 414  width= 23 ) (actual time= 0 . 025 .. 1 . 250  rows= 414  loops= 1 )

->  Hash  (cost= 59 . 13 .. 59 . 13  rows= 402  width= 11 ) (actual time= 2 . 039 .. 2 . 039  rows= 402  loops= 1 )
    ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 59 . 13  rows= 402  width= 11 )

здесь неоптимально.

попробуйте после выполнения двух команд:
set enable_seqscan to off;
set enable_hashjoin to off;

...

Рейтинг:

0 / 0

29.07.2008, 13:34

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

Оптимизация большого запроса возможна?

#35457836

UKY

Гость

После долгих перезапусков выборки удалось получить вот такую вот картину (:

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

Unique  (cost= 1312 . 62 .. 1313 . 79  rows= 117  width= 376 ) (actual time= 7 . 385 .. 7 . 684  rows= 29  loops= 1 )
  ->  Sort  (cost= 1312 . 62 .. 1312 . 91  rows= 117  width= 376 ) (actual time= 7 . 381 .. 7 . 479  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Nested Loop  (cost= 1236 . 98 .. 1308 . 60  rows= 117  width= 376 ) (actual time= 4 . 686 .. 6 . 903  rows= 59  loops= 1 )
              Join Filter: (services.type = service.id)
              ->  Index Scan using service_pkey on service  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 13 ) (actual time= 0 . 014 .. 0 . 017  rows= 1  loops= 1 )
              ->  Merge Join  (cost= 1236 . 98 .. 1298 . 87  rows= 117  width= 371 ) (actual time= 4 . 660 .. 6 . 652  rows= 59  loops= 1 )
                    Merge Cond: (services.server = servers.id)
                    ->  Sort  (cost= 1236 . 98 .. 1237 . 28  rows= 117  width= 368 ) (actual time= 4 . 601 .. 4 . 701  rows= 59  loops= 1 )
                          Sort Key: services.server
                          Sort Method:  quicksort  Memory: 55kB
                          ->  Merge Join  (cost= 1179 . 28 .. 1232 . 96  rows= 117  width= 368 ) (actual time= 2 . 522 .. 4 . 431  rows= 59  loops= 1 )
                                Merge Cond: (services.id = folders.service)
                                ->  Index Scan using services_pkey on services  (cost= 0 . 00 .. 51 . 28  rows= 414  width= 23 ) (actual time= 0 . 016 .. 0 . 824  rows= 413  loops= 1 )
                                ->  Sort  (cost= 1179 . 15 .. 1179 . 44  rows= 117  width= 353 ) (actual time= 2 . 459 .. 2 . 569  rows= 59  loops= 1 )
                                      Sort Key: folders.service
                                      Sort Method:  quicksort  Memory: 55kB
                                      ->  Nested Loop  (cost= 0 . 00 .. 1175 . 13  rows= 117  width= 353 ) (actual time= 0 . 390 .. 2 . 280  rows= 59  loops= 1 )
                                            ->  Nested Loop  (cost= 0 . 00 .. 714 . 17  rows= 117  width= 290 ) (actual time= 0 . 371 .. 1 . 284  rows= 59  loops= 1 )
                                                  ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 0 . 354 .. 0 . 718  rows= 19  loops= 1 )
                                                        ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 0 . 330 .. 0 . 362  rows= 20  loops= 1 )
                                                        ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 009 .. 0 . 011  rows= 1  loops= 20 )
                                                              Index Cond: (tths.id = tth_search.tth_search)
                                                  ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 26 . 82  rows= 6  width= 286 ) (actual time= 0 . 008 .. 0 . 015  rows= 3  loops= 19 )
                                                        Index Cond: (files.tth = tths.id)
                                            ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 3 . 93  rows= 1  width= 75 ) (actual time= 0 . 008 .. 0 . 010  rows= 1  loops= 59 )
                                                  Index Cond: (folders.id = files.path)
                    ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 59 . 13  rows= 402  width= 11 ) (actual time= 0 . 016 .. 0 . 790  rows= 410  loops= 1 )
Total runtime:  7 . 924  ms

...

Рейтинг:

0 / 0

29.07.2008, 13:51

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

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

все

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

start [/forum/topic.php?fid=53&msg=35454029&tid=1995161]:	0ms
get settings:	7ms
get forum list:	16ms
check forum access:	4ms
check topic access:	4ms
track hit:	57ms
get topic data:	12ms
get forum data:	3ms
get page messages:	56ms
get tp. blocked users:	1ms
others:	277ms

total:	437ms