ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
10 сообщений из 10, страница 1 из 1
Оптимизация запроса
    #38813342
vlad_gg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vlad_gg
Гость
Добрый день, прошу совета по ускорению запроса.

1) Версия ос, PG, железо:
 
PG 9.3 на MacOS 10.9.5 (8GB ram)


2) Настройки PG
 
-- postgresql.conf
Autovacuum on
Postgresql.conf:
default_statistics_target = 50
maintenance_work_mem = 448MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 5632MB
work_mem = 44MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 1792MB
max_connections = 80


-- SELECT name,setting FROM pg_settings WHERE source NOT IN ('default','override');
"effective_cache_size";"720896"
"maintenance_work_mem";"458752"
"max_connections";"80"
"max_stack_depth";"2048"
"search_path";""$user", public, topology"
"shared_buffers";"229376"
"wal_buffers";"1024"
"work_mem";"45056"


3) Структура базы для данного запроса (остальные таблицы убрал для понятности)
 


5) Описания цели запроса
 
Я храню данные об организациях. У организации есть город в котором она расположена
и могут быть теги. Запросом я хочу найти теги, которые находятся в городах, ближайших к городу
организации.

Например:
Организация "Кока Коала" расположена в г. Москва и к ней привязаны теги "макароны" и "упаковка".
А рядом с Москвой есть город Иваново и Петрово. В Иваново есть компания "X1" с тегом "макароны", а в Петрово есть компания "Y2" с тегом "упаковка". В результате запроса в выборку попадут "макароны Иваново" и "упаковка Петрово" .


6) Запрос
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT DISTINCT ON("tag"."id") "tag"."id" AS tag_id,
                "city"."id" AS city_id,
                "city"."name" AS city_name,
                "city"."state_short_name" AS state_short_name,
                "tag"."name_singular" AS tag_name_singular,
                "tag"."name_plural" AS tag_name_plural
FROM "tag_organization"
INNER JOIN "tag" ON "tag"."id" = "tag_organization"."tag_id"
AND "tag_organization"."tag_id" IN (41020,
                                    175458)
INNER JOIN "organization" ON organization.id=organization_id
INNER JOIN
  (SELECT city.id,
          city.name,
          city.state_short_name
   FROM city
   WHERE city.id != '1017900'
   ORDER BY "city"."location" <-> st_setsrid(ST_GeomFromText('POINT (-83.7340729999999951 42.7927109999999971)'),4326) LIMIT 5500) city ON (organization.city_id = city.id) LIMIT 10;



-- [!] В подзапросе указан такой большой лимит (5500) из-за того что далеко не у всех компаний есть похожий тег и ближайшая компания может оказаться достаточно далеко от города по которому мы ищем.


7) Explain analyze
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Limit  (cost=1.56..985.00 rows=10 width=42)
  ->  Unique  (cost=1.56..61663.14 rows=627 width=42)
        ->  Nested Loop  (cost=1.56..61661.57 rows=627 width=42)
              Join Filter: (organization.city_id = city.id)
              ->  Nested Loop  (cost=1.28..9330.41 rows=627 width=30)
                    ->  Nested Loop  (cost=0.85..4065.99 rows=627 width=30)
                          ->  Index Only Scan using tag_organization_tag_id_organization_id_key1 on tag_organization  (cost=0.43..19.84 rows=627 width=8)
                                Index Cond: (tag_id = ANY ('{41020,175458}'::integer[]))
                          ->  Index Scan using tag_pkey1 on tag  (cost=0.42..6.44 rows=1 width=26)"
                                Index Cond: (id = tag_organization.tag_id)
                    ->  Index Scan using organization_pkey1 on organization  (cost=0.43..8.39 rows=1 width=8)
                          Index Cond: (id = tag_organization.organization_id)
              ->  Materialize  (cost=0.28..617.41 rows=5500 width=16)
                    ->  Subquery Scan on city  (cost=0.28..589.91 rows=5500 width=16)
                          ->  Limit  (cost=0.28..534.91 rows=5500 width=48)
                                ->  Index Scan using city_location_id on city city_1  (cost=0.28..3190.79 rows=32822 width=48)
                                      Order By: (location <-> '0101000020E61000001AF8510DFBEE54C0F816D68D77654540'::geometry)
                                      Filter: (id <> 1017900)

...
Рейтинг: 0 / 0
21.11.2014, 15:31:43
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38813741
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
vlad_gg,

вы explain привели а не explain analyze
...
Рейтинг: 0 / 0
21.11.2014, 21:52:59
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38813770
vlad_gg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vlad_gg
Гость
Maxim Boguk, исправляюсь

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
Limit  (cost=1.56..1022.27 rows=10 width=42) (actual time=922.941..3235.280 rows=2 loops=1)
  ->  Unique  (cost=1.56..62979.35 rows=617 width=42) (actual time=922.938..3235.274 rows=2 loops=1)
        ->  Nested Loop  (cost=1.56..62977.81 rows=617 width=42) (actual time=922.935..3235.242 rows=14 loops=1)
              Join Filter: (organization.city_id = city.id)
              Rows Removed by Join Filter: 115486
              ->  Nested Loop  (cost=1.28..11475.67 rows=617 width=30) (actual time=198.966..1736.378 rows=21 loops=1)
                    ->  Nested Loop  (cost=0.85..6291.85 rows=617 width=30) (actual time=107.333..457.415 rows=21 loops=1)
                          ->  Index Scan using tag_organization_76f094bc on tag_organization  (cost=0.43..2298.20 rows=617 width=8) (actual time=107.242..456.439 rows=21 loops=1)
                                Index Cond: (tag_id = ANY ('{41020,175458}'::integer[]))
                          ->  Index Scan using tag_pkey1 on tag  (cost=0.42..6.46 rows=1 width=26) (actual time=0.032..0.034 rows=1 loops=21)
                                Index Cond: (id = tag_organization.tag_id)
                    ->  Index Scan using organization_pkey1 on organization  (cost=0.43..8.39 rows=1 width=8) (actual time=60.893..60.897 rows=1 loops=21)
                          Index Cond: (id = tag_organization.organization_id)
              ->  Materialize  (cost=0.28..613.39 rows=5500 width=16) (actual time=3.590..68.979 rows=5500 loops=21)
                    ->  Subquery Scan on city  (cost=0.28..585.89 rows=5500 width=16) (actual time=75.337..1416.806 rows=5500 loops=1)
                          ->  Limit  (cost=0.28..530.89 rows=5500 width=48) (actual time=75.335..1413.459 rows=5500 loops=1)
                                ->  Index Scan using city_location_id on city city_1  (cost=0.28..3166.79 rows=32822 width=48) (actual time=75.332..1411.111 rows=5500 loops=1)
                                      Order By: (location <-> '0101000020E61000001AF8510DFBEE54C0F816D68D77654540'::geometry)
                                      Filter: (id <> 1017900)
                                      Rows Removed by Filter: 1
Total runtime: 3235.743 ms
...
Рейтинг: 0 / 0
21.11.2014, 22:52:37
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38813958
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
vlad_gg,

1.а есть ли в наличии индексы по

organization(id)
tag_organization(tag_id)

2. запрос с distinct on () без order by некорректен в принципе (почитайте документацию)

3.попробуйте вместе с
AND "tag_organization"."tag_id" IN (41020, 175458)
заодно добавить
AND "tag"."id" IN (41020, 175458)

PS: времена (actual time=107.242..456.439 rows=21 loops=1) и (actual time=60.893..60.897 rows=1 loops=21) намекают на или проблемы с диском или неправильные индексы.

PPS: если запрос выполнить раза 3 последовательно (explain analyze) Он быстрее не становится?
...
Рейтинг: 0 / 0
22.11.2014, 11:50:09
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38814091
а так
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
а так
Гость
vlad_gg<>PG 9.3<>
Код: 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.
SELECT 
	"tag"."id" AS tag_id,
	"city"."id" AS city_id,
	"city"."name" AS city_name,
	"city"."state_short_name" AS state_short_name,
	"tag"."name_singular" AS tag_name_singular,
	"tag"."name_plural" AS tag_name_plural	
FROM tag
,LATERAL 
(	SELECT 	city.id,
		city.name,
		city.state_short_name
	FROM city
	INNER JOIN "organization" ON (organization.city_id = city.id) 
	INNER JOIN  "tag_organization" ON organization.id=organization_id
	WHERE
		"tag_organization"."tag_id" = "tag"."id"
		AND "tag_organization"."tag_id" IN (41020,175458)
		AND city.id != '1017900' --!!!! так и есть -- строка -- и уходите от pkey-scan
	
	ORDER BY "city"."location" <-> st_setsrid(ST_GeomFromText('POINT (-83.7340729999999951 42.7927109999999971)'),4326)
	LIMIT 1 /* DISTINCT ON "tag"."id" = "tag_organization"."tag_id" */
) city
WHERE
	tag.id IN (41020,175458);


?
...
Рейтинг: 0 / 0
22.11.2014, 15:59:00
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38814092
описался
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
описался
Гость
а так
Код: sql
1.
		AND city.id != '1017900' --!!!! так и есть -- строка -- иЛИ уходите от pkey-scan ?


?
...
Рейтинг: 0 / 0
22.11.2014, 16:02:12
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38814263
vlad_gg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vlad_gg
Гость
Maxim Bogukvlad_gg,

1.а есть ли в наличии индексы по

organization(id)
tag_organization(tag_id)

2. запрос с distinct on () без order by некорректен в принципе (почитайте документацию)

3.попробуйте вместе с
AND "tag_organization"."tag_id" IN (41020, 175458)
заодно добавить
AND "tag"."id" IN (41020, 175458)

PS: времена (actual time=107.242..456.439 rows=21 loops=1) и (actual time=60.893..60.897 rows=1 loops=21) намекают на или проблемы с диском или неправильные индексы.

PPS: если запрос выполнить раза 3 последовательно (explain analyze) Он быстрее не становится?

1. Да, есть оба

2. Ок

3. Попробовал, выполняется быстрее.
было
"Limit (cost=1.56..1040.97 rows=10 width=42)"
стало
"Limit (cost=653.51..653.52 rows=1 width=42)"

re: pps - становится
...
Рейтинг: 0 / 0
23.11.2014, 00:42:49
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38814264
vlad_gg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vlad_gg
Гость
а такvlad_gg<>PG 9.3<>
Код: 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.
SELECT 
	"tag"."id" AS tag_id,
	"city"."id" AS city_id,
	"city"."name" AS city_name,
	"city"."state_short_name" AS state_short_name,
	"tag"."name_singular" AS tag_name_singular,
	"tag"."name_plural" AS tag_name_plural	
FROM tag
,LATERAL 
(	SELECT 	city.id,
		city.name,
		city.state_short_name
	FROM city
	INNER JOIN "organization" ON (organization.city_id = city.id) 
	INNER JOIN  "tag_organization" ON organization.id=organization_id
	WHERE
		"tag_organization"."tag_id" = "tag"."id"
		AND "tag_organization"."tag_id" IN (41020,175458)
		AND city.id != '1017900' --!!!! так и есть -- строка -- и уходите от pkey-scan
	
	ORDER BY "city"."location" <-> st_setsrid(ST_GeomFromText('POINT (-83.7340729999999951 42.7927109999999971)'),4326)
	LIMIT 1 /* DISTINCT ON "tag"."id" = "tag_organization"."tag_id" */
) city
WHERE
	tag.id IN (41020,175458);


?

Спасибо большое, запрос летает.
EXPLAIN ANALYSE:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
Nested Loop  (cost=22.11..56.30 rows=2 width=42) (actual time=0.608..0.690 rows=2 loops=1)
  ->  Index Scan using tag_pkey1 on tag  (cost=0.42..12.88 rows=2 width=26) (actual time=0.015..0.028 rows=2 loops=1)
        Index Cond: (id = ANY ('{41020,175458}'::integer[]))
  ->  Limit  (cost=21.69..21.69 rows=1 width=48) (actual time=0.326..0.326 rows=1 loops=2)
        ->  Sort  (cost=21.69..21.69 rows=1 width=48) (actual time=0.324..0.324 rows=1 loops=2)
              Sort Key: ((city.location <-> '0101000020E61000001AF8510DFBEE54C0F816D68D77654540'::geometry))
              Sort Method: quicksort  Memory: 25kB
              ->  Nested Loop  (cost=1.15..21.68 rows=1 width=48) (actual time=0.068..0.305 rows=10 loops=2)
                    ->  Nested Loop  (cost=0.86..21.35 rows=1 width=4) (actual time=0.029..0.184 rows=10 loops=2)
                          ->  Index Only Scan using tag_organization_tag_id_organization_id_key1 on tag_organization  (cost=0.43..12.89 rows=1 width=4) (actual time=0.015..0.035 rows=10 loops=2)
                                Index Cond: ((tag_id = ANY ('{41020,175458}'::integer[])) AND (tag_id = tag.id))
                                Heap Fetches: 21
                          ->  Index Scan using organization_pkey1 on organization  (cost=0.43..8.45 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=21)
                                Index Cond: (id = tag_organization.organization_id)
                    ->  Index Scan using city_pkey1 on city  (cost=0.29..0.31 rows=1 width=48) (actual time=0.007..0.008 rows=1 loops=21)
                          Index Cond: (id = organization.city_id)
                          Filter: (id <> 1017900)
                          Rows Removed by Filter: 0
Total runtime: 0.788 ms



Что касается id записаным как строка, то это конечно-же моя ошибка. Но я не увидел разницу в производительности если писать id без кавычек.
...
Рейтинг: 0 / 0
23.11.2014, 00:48:01
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38814313
vlad_gg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vlad_gg
Гость
Хочу еще раз поблагодарить неизвестного пользователя и Maxim Boguk за помощь.
У вас крутое Postgres кунг-фу :)
...
Рейтинг: 0 / 0
23.11.2014, 10:44:23
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38814350
Лопата
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Лопата
Участник
vlad_gg,

У вас перестал использоваться гист-индекс по геометриям

-- скорее всего у вас мало введено организаций, мало тегов и совпадений по тегам, и , наоборот, много городов, не заполненных еще организациями.

т.ч. радоваться вам рано.
если ситуация будет такой почти всегда -- то это хорошо

но если в каждом городе будут 100-ни или 1000 организаций с 100-ни раз перекрывающимися тегами -- надо посмотреть, как себя поведёт планировщик (перейдет ли он на nested loop по гист-индексу, с фильтрацией по ключам, и что это даст).
...
Рейтинг: 0 / 0
23.11.2014, 12:51:23
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=119&tid=1998331]:
 0ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 43ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 46ms
get tp. blocked users:
 2ms
others: 247ms
total:  378ms
созд. / загр.: 378ms
x
x
Закрыть


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