Оптимизация запроса

vlad_gg
Дата: 21.11.2014 15:31:43
Добрый день, прошу совета по ускорению запроса.

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) Запрос
+
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
+
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)
Maxim Boguk
Дата: 21.11.2014 21:52:59
vlad_gg,

вы explain привели а не explain analyze
vlad_gg
Дата: 21.11.2014 22:52:37
Maxim Boguk, исправляюсь

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
Maxim Boguk
Дата: 22.11.2014 11:50:09
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) Он быстрее не становится?
а так
Дата: 22.11.2014 15:59:00
vlad_gg
<>PG 9.3<>

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);

?
описался
Дата: 22.11.2014 16:02:12
а так
		AND city.id != '1017900' --!!!! так и есть -- строка -- иЛИ уходите от pkey-scan ?

?
vlad_gg
Дата: 23.11.2014 00:42:49
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) Он быстрее не становится?


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 - становится
vlad_gg
Дата: 23.11.2014 00:48:01
а так
vlad_gg
<>PG 9.3<>

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:
+
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 без кавычек.
vlad_gg
Дата: 23.11.2014 10:44:23
Хочу еще раз поблагодарить неизвестного пользователя и Maxim Boguk за помощь.
У вас крутое Postgres кунг-фу :)
лопата
Дата: 23.11.2014 12:51:23
vlad_gg,

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

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

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

но если в каждом городе будут 100-ни или 1000 организаций с 100-ни раз перекрывающимися тегами -- надо посмотреть, как себя поведёт планировщик (перейдет ли он на nested loop по гист-индексу, с фильтрацией по ключам, и что это даст).