Не позволяйте PostgreSQL иногда выбирать неверный план запроса
У меня странная проблема с производительностью PostgreSQL для запроса, использующего PostgreSQL 8.4.9. Этот запрос выбирает набор точек в трехмерном объеме, используя LEFT OUTER JOIN добавить столбец связанных идентификаторов, где этот связанный идентификатор существует. Небольшие изменения в x Диапазон может привести к тому, что PostgreSQL выберет другой план запроса, который занимает время выполнения от 0,01 до 50 секунд. Это вопрос в вопросе:
SELECT treenode.id AS id,
treenode.parent_id AS parentid,
(treenode.location).x AS x,
(treenode.location).y AS y,
(treenode.location).z AS z,
treenode.confidence AS confidence,
treenode.user_id AS user_id,
treenode.radius AS radius,
((treenode.location).z - 50) AS z_diff,
treenode_class_instance.class_instance_id AS skeleton_id
FROM treenode LEFT OUTER JOIN
(treenode_class_instance INNER JOIN
class_instance ON treenode_class_instance.class_instance_id
= class_instance.id
AND class_instance.class_id = 7828307)
ON (treenode_class_instance.treenode_id = treenode.id
AND treenode_class_instance.relation_id = 7828321)
WHERE treenode.project_id = 4
AND (treenode.location).x >= 8000
AND (treenode.location).x <= (8000 + 4736)
AND (treenode.location).y >= 22244
AND (treenode.location).y <= (22244 + 3248)
AND (treenode.location).z >= 0
AND (treenode.location).z <= 100
ORDER BY parentid DESC, id, z_diff
LIMIT 400;
Этот запрос занимает около минуты, и, если я добавлю EXPLAIN в начале этого запроса, похоже, используется следующий план запроса:
Limit (cost=56185.16..56185.17 rows=1 width=89)
-> Sort (cost=56185.16..56185.17 rows=1 width=89)
Sort Key: treenode.parent_id, treenode.id, (((treenode.location).z - 50::double precision))
-> Nested Loop Left Join (cost=6715.16..56185.15 rows=1 width=89)
Join Filter: (treenode_class_instance.treenode_id = treenode.id)
-> Bitmap Heap Scan on treenode (cost=148.55..184.16 rows=1 width=81)
Recheck Cond: (((location).x >= 8000::double precision) AND ((location).x <= 12736::double precision) AND ((location).z >= 0::double precision) AND ((location).z <= 100::double precision))
Filter: (((location).y >= 22244::double precision) AND ((location).y <= 25492::double precision) AND (project_id = 4))
-> BitmapAnd (cost=148.55..148.55 rows=9 width=0)
-> Bitmap Index Scan on location_x_index (cost=0.00..67.38 rows=2700 width=0)
Index Cond: (((location).x >= 8000::double precision) AND ((location).x <= 12736::double precision))
-> Bitmap Index Scan on location_z_index (cost=0.00..80.91 rows=3253 width=0)
Index Cond: (((location).z >= 0::double precision) AND ((location).z <= 100::double precision))
-> Hash Join (cost=6566.61..53361.69 rows=211144 width=16)
Hash Cond: (treenode_class_instance.class_instance_id = class_instance.id)
-> Seq Scan on treenode_class_instance (cost=0.00..25323.79 rows=969285 width=16)
Filter: (relation_id = 7828321)
-> Hash (cost=5723.54..5723.54 rows=51366 width=8)
-> Seq Scan on class_instance (cost=0.00..5723.54 rows=51366 width=8)
Filter: (class_id = 7828307)
(20 rows)
Однако, если я заменю 8000 в x условие дальности с 10644запрос выполняется за доли секунды и использует этот план запроса:
Limit (cost=58378.94..58378.95 rows=2 width=89)
-> Sort (cost=58378.94..58378.95 rows=2 width=89)
Sort Key: treenode.parent_id, treenode.id, (((treenode.location).z - 50::double precision))
-> Hash Left Join (cost=57263.11..58378.93 rows=2 width=89)
Hash Cond: (treenode.id = treenode_class_instance.treenode_id)
-> Bitmap Heap Scan on treenode (cost=231.12..313.44 rows=2 width=81)
Recheck Cond: (((location).z >= 0::double precision) AND ((location).z <= 100::double precision) AND ((location).x >= 10644::double precision) AND ((location).x <= 15380::double precision))
Filter: (((location).y >= 22244::double precision) AND ((location).y <= 25492::double precision) AND (project_id = 4))
-> BitmapAnd (cost=231.12..231.12 rows=21 width=0)
-> Bitmap Index Scan on location_z_index (cost=0.00..80.91 rows=3253 width=0)
Index Cond: (((location).z >= 0::double precision) AND ((location).z <= 100::double precision))
-> Bitmap Index Scan on location_x_index (cost=0.00..149.95 rows=6157 width=0)
Index Cond: (((location).x >= 10644::double precision) AND ((location).x <= 15380::double precision))
-> Hash (cost=53361.69..53361.69 rows=211144 width=16)
-> Hash Join (cost=6566.61..53361.69 rows=211144 width=16)
Hash Cond: (treenode_class_instance.class_instance_id = class_instance.id)
-> Seq Scan on treenode_class_instance (cost=0.00..25323.79 rows=969285 width=16)
Filter: (relation_id = 7828321)
-> Hash (cost=5723.54..5723.54 rows=51366 width=8)
-> Seq Scan on class_instance (cost=0.00..5723.54 rows=51366 width=8)
Filter: (class_id = 7828307)
(21 rows)
Я далеко не эксперт в разборе этих планов запросов, но, кажется, явное различие заключается в том, что с одним x Диапазон он использует Hash Left Join для LEFT OUTER JOIN (что очень быстро), в то время как с другим диапазоном он использует Nested Loop Left Join (что, кажется, очень медленно). В обоих случаях запросы возвращают около 90 строк. Если я сделаю SET ENABLE_NESTLOOP TO FALSE до медленной версии запроса он идет очень быстро, но я понимаю, что использование этого параметра в целом - плохая идея.
Могу ли я, например, создать определенный индекс, чтобы повысить вероятность того, что планировщик запросов выберет явно более эффективную стратегию? Кто-нибудь может подсказать, почему планировщик запросов PostgreSQL должен выбирать такую плохую стратегию для одного из этих запросов? Ниже я включил детали схемы, которые могут быть полезны.
Таблица treenode имеет 900000 строк и определяется следующим образом:
Table "public.treenode"
Column | Type | Modifiers
---------------+--------------------------+------------------------------------------------------
id | bigint | not null default nextval('concept_id_seq'::regclass)
user_id | bigint | not null
creation_time | timestamp with time zone | not null default now()
edition_time | timestamp with time zone | not null default now()
project_id | bigint | not null
location | double3d | not null
parent_id | bigint |
radius | double precision | not null default 0
confidence | integer | not null default 5
Indexes:
"treenode_pkey" PRIMARY KEY, btree (id)
"treenode_id_key" UNIQUE, btree (id)
"location_x_index" btree (((location).x))
"location_y_index" btree (((location).y))
"location_z_index" btree (((location).z))
Foreign-key constraints:
"treenode_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES treenode(id)
Referenced by:
TABLE "treenode_class_instance" CONSTRAINT "treenode_class_instance_treenode_id_fkey" FOREIGN KEY (treenode_id) REFERENCES treenode(id) ON DELETE CASCADE
TABLE "treenode" CONSTRAINT "treenode_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES treenode(id)
Triggers:
on_edit_treenode BEFORE UPDATE ON treenode FOR EACH ROW EXECUTE PROCEDURE on_edit()
Inherits: location
double3d составной тип определяется следующим образом:
Composite type "public.double3d"
Column | Type
--------+------------------
x | double precision
y | double precision
z | double precision
Две другие таблицы, участвующие в объединении: treenode_class_instance:
Table "public.treenode_class_instance"
Column | Type | Modifiers
-------------------+--------------------------+------------------------------------------------------
id | bigint | not null default nextval('concept_id_seq'::regclass)
user_id | bigint | not null
creation_time | timestamp with time zone | not null default now()
edition_time | timestamp with time zone | not null default now()
project_id | bigint | not null
relation_id | bigint | not null
treenode_id | bigint | not null
class_instance_id | bigint | not null
Indexes:
"treenode_class_instance_pkey" PRIMARY KEY, btree (id)
"treenode_class_instance_id_key" UNIQUE, btree (id)
"idx_class_instance_id" btree (class_instance_id)
Foreign-key constraints:
"treenode_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id) REFERENCES class_instance(id) ON DELETE CASCADE
"treenode_class_instance_relation_id_fkey" FOREIGN KEY (relation_id) REFERENCES relation(id)
"treenode_class_instance_treenode_id_fkey" FOREIGN KEY (treenode_id) REFERENCES treenode(id) ON DELETE CASCADE
"treenode_class_instance_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
Triggers:
on_edit_treenode_class_instance BEFORE UPDATE ON treenode_class_instance FOR EACH ROW EXECUTE PROCEDURE on_edit()
Inherits: relation_instance
... а также class_instance:
Table "public.class_instance"
Column | Type | Modifiers
---------------+--------------------------+------------------------------------------------------
id | bigint | not null default nextval('concept_id_seq'::regclass)
user_id | bigint | not null
creation_time | timestamp with time zone | not null default now()
edition_time | timestamp with time zone | not null default now()
project_id | bigint | not null
class_id | bigint | not null
name | character varying(255) | not null
Indexes:
"class_instance_pkey" PRIMARY KEY, btree (id)
"class_instance_id_key" UNIQUE, btree (id)
Foreign-key constraints:
"class_instance_class_id_fkey" FOREIGN KEY (class_id) REFERENCES class(id)
"class_instance_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
Referenced by:
TABLE "class_instance_class_instance" CONSTRAINT "class_instance_class_instance_class_instance_a_fkey" FOREIGN KEY (class_instance_a) REFERENCES class_instance(id) ON DELETE CASCADE
TABLE "class_instance_class_instance" CONSTRAINT "class_instance_class_instance_class_instance_b_fkey" FOREIGN KEY (class_instance_b) REFERENCES class_instance(id) ON DELETE CASCADE
TABLE "connector_class_instance" CONSTRAINT "connector_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id) REFERENCES class_instance(id)
TABLE "treenode_class_instance" CONSTRAINT "treenode_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id) REFERENCES class_instance(id) ON DELETE CASCADE
Triggers:
on_edit_class_instance BEFORE UPDATE ON class_instance FOR EACH ROW EXECUTE PROCEDURE on_edit()
Inherits: concept
6 ответов
Если планировщик запросов принимает неверные решения, это в основном одна из двух вещей:
1. Статистика неточная.
Вы бежите ANALYZE довольно? Также популярный в его комбинированной форме VACUUM ANALYZE, Если автовакуум включен (это значение по умолчанию в современных Postgres), ANALYZE запускается автоматически. Но учтите:
(Лучшие два ответа все еще применимы к Postgres 9.6.)
Если ваша таблица большая, а распределение данных нерегулярное, default_statistics_target может помочь Или, скорее, просто установите цель статистики для соответствующих столбцов (те, в WHERE или же JOIN пункты ваших запросов, в основном):
ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 400; -- calibrate number
Цель может быть установлена в диапазоне от 0 до 10000;
Бежать ANALYZE снова после этого (на соответствующих таблицах).
2. Настройки стоимости для плановых оценок отключены.
Прочитайте главу " Константы стоимости планировщика" в руководстве.
Посмотрите на главы default_statistics_target и random_page_cost на этой в целом полезной странице Вики PostgreSQL.
Есть много других возможных причин, но это наиболее распространенные на сегодняшний день.
Я скептически отношусь к тому, что это имеет отношение к плохой статистике, если не учитывать комбинацию статистики базы данных и вашего пользовательского типа данных.
Я думаю, что PostgreSQL выбирает соединение с вложенным циклом, потому что он смотрит на предикаты (treenode.location).x >= 8000 AND (treenode.location).x <= (8000 + 4736) и делает что-то напуганное в арифметике вашего сравнения. Вложенный цикл обычно будет использоваться, когда у вас есть небольшой объем данных на внутренней стороне объединения.
Но как только вы переключите константу на 10736, вы получите другой план. Всегда возможно, что план достаточно сложен, и в него вступает Genetic Query Optimization (GEQO), и вы видите побочные эффекты недетерминированного построения плана. Существует достаточно расхождений в порядке оценки в запросах, чтобы я мог думать, что это происходит.
Одним из вариантов было бы проверить использование параметризованного / подготовленного оператора для этого вместо использования специального кода. Поскольку вы работаете в трехмерном пространстве, вы также можете рассмотреть возможность использования PostGIS. Хотя это может быть излишним, оно также может предоставить вам производительность, необходимую для правильной работы этих запросов.
Хотя принудительное поведение планировщика не лучший выбор, иногда мы принимаем лучшие решения, чем программное обеспечение.
Что сказал Эрвин о статистике. Также:
ORDER BY parentid DESC, id, z_diff
Сортировка по
parentid DESC, id, z
может дать оптимизатору немного больше места для перемешивания. (Я не думаю, что это будет иметь большое значение, так как это последний срок, и сортировка не такая уж дорогая, но вы можете попробовать)
Я не уверен, что это источник вашей проблемы, но похоже, что в планировщике запросов postgres произошли некоторые изменения между версиями 8.4.8 и 8.4.9. Вы можете попробовать использовать более старую версию и посмотреть, если это имеет значение.
http://postgresql.1045698.n5.nabble.com/BUG-6275-Horrible-performance-regression-td4944891.html
Не забудьте повторно проанализировать ваши таблицы, если вы измените версию.
+1 для настройки целевой статистики и действий ANALYZE. И для PostGIS (для OP).
Но также, это не совсем связано с исходным вопросом, но все же, если кто-то сюда попадает, чтобы узнать, как в целом справиться с неточными оценками количества строк планировщика в сложных запросах, что приводит к нежелательным планам. Вариант может заключаться в том, чтобы обернуть часть исходного запроса в функцию и установить ееROWSвариант к чему-то более-менее ожидаемому. Я никогда этого не делал, но, видимо, должно работать.
Также есть директивы оценки строк в pg_hint_plan. Я бы не советовал планировщику подсказки в целом, но корректировка оценки строк - более мягкий вариант.
И, наконец, для принудительного сканирования вложенных циклов иногда можно выполнить LATERAL JOIN с LIMIT N или просто OFFSET 0внутри подзапроса. Это даст вам то, что вы хотите. Но обратите внимание, это очень грубый трюк. В какой-то момент это приведет к снижению производительности, ЕСЛИ условия изменятся - из-за роста таблицы или просто другого распределения данных. Тем не менее, это может быть хорошим вариантом, чтобы срочно облегчить работу устаревшей системы.
В случае неудачного плана вы всегда можете прибегнуть к расширению pg_hint_plan. Он предоставляет подсказки в стиле Oracle для PostgreSQL.