Postgres не использует индекс для сортировки данных

ProgramBox

Postgres не использует индекс для сортировки данных

Post author:admin
Запись опубликована:29 марта, 2023
Post category:Вопросы по программированию

#postgresql #indexing #sql-order-by

#postgresql #индексирование #sql-order-by

Вопрос:

У меня есть таблица learners , в которой около 3,2 миллиона строк. Эта таблица содержит информацию, связанную с пользователем, такую как имя и адрес электронной почты. Мне нужно оптимизировать некоторые запросы, которые используют order by для некоторого столбца. Итак, для тестирования я создал temp_learners таблицу с 0,8 миллионами строк. Я создал два индекса в этой таблице:

 CREATE UNIQUE INDEX "temp_learners_companyId_userId_idx"
  ON temp_learners ("companyId" ASC, "userId" ASC, "learnerUserName" ASC, "learnerEmailId" ASC);

 CREATE INDEX temp_learners_company_name_email_index
  ON temp_learners ("companyId", "learnerUserName", "learnerEmailId");

Второй индекс предназначен только для тестирования.
Теперь, когда я запускаю этот запрос:

 SELECT *
FROM temp_learners
WHERE "companyId" = 909666665757230431 AND "userId" IN (
                                                        4990609084216745771,
                                                        4990610022492247987,
                                                        4990609742667096366,
                                                        4990609476136523663,
                                                        5451985767018841230,
                                                        5451985767078553638,
                                                        5270390122102920730,
                                                        4763688819142650938,
                                                        5056979692501246449,
                                                        5279569274741647114,
                                                        5031660827132289520,
                                                        4862889373349389098,
                                                        5299864070077160421,
                                                        4740222596778406913,
                                                        5320170488686569878,
                                                        5270367618320474818,
                                                        5320170488587895729,
                                                        5228888485293847415,
                                                        4778050469432720821,
                                                        5270392314970177842,
                                                        4849087862439244546,
                                                        5270392117430427860,
                                                        5270351184072717902,
                                                        5330263074228870897,
                                                        4763688829301614114,
                                                        4763684609695916489,
                                                        5270390232949727716
  ) ORDER BY "learnerUserName","learnerEmailId";

План запроса, используемый db, таков:

 Sort  (cost=138.75..138.76 rows=4 width=1581) (actual time=0.169..0.171 rows=27 loops=1)
"  Sort Key: ""learnerUserName"", ""learnerEmailId"""
  Sort Method: quicksort  Memory: 73kB
  ->  Index Scan using "temp_learners_companyId_userId_idx" on temp_learners  (cost=0.55..138.71 rows=4 width=1581) (actual time=0.018..0.112 rows=27 loops=1)
"        Index Cond: ((""companyId"" = '909666665757230431'::bigint) AND (""userId"" = ANY ('{4990609084216745771,4990610022492247987,4990609742667096366,4990609476136523663,5451985767018841230,5451985767078553638,5270390122102920730,4763688819142650938,5056979692501246449,5279569274741647114,5031660827132289520,4862889373349389098,5299864070077160421,4740222596778406913,5320170488686569878,5270367618320474818,5320170488587895729,5228888485293847415,4778050469432720821,5270392314970177842,4849087862439244546,5270392117430427860,5270351184072717902,5330263074228870897,4763688829301614114,4763684609695916489,5270390232949727716}'::bigint[])))"
Planning time: 0.116 ms
Execution time: 0.191 ms

В этом случае он не сортирует по индексам.
Но когда я запускаю этот запрос

 SELECT *
FROM temp_learners
WHERE "companyId" = 909666665757230431
   ORDER BY "learnerUserName","learnerEmailId" limit 500;

Этот запрос использует индексы при сортировке.

 Limit  (cost=0.42..1360.05 rows=500 width=1581) (actual time=0.018..0.477 rows=500 loops=1)
  ->  Index Scan using temp_learners_company_name_email_index on temp_learners  (cost=0.42..332639.30 rows=122327 width=1581) (actual time=0.018..0.442 rows=500 loops=1)
        Index Cond: ("companyId" = '909666665757230431'::bigint)
Planning time: 0.093 ms
Execution time: 0.513 ms

Чего я не могу понять, так это почему postgre не использует индекс в первом запросе? Кроме того, я хочу прояснить, что обычным вариантом использования этой таблицы learner является объединение с другими таблицами. Итак, первый запрос, который я написал, больше похож на уравнение объединения. Так, например,

 SELECT *
FROM temp_learners AS l
INNER JOIN entity_learners_basic AS elb
ON l."companyId" = elb."companyId" AND l."userId" = elb."userId"
WHERE l."companyId" = 909666665757230431 AND elb."gameId" = 1050403501267716928
ORDER BY "learnerUserName", "learnerEmailId" limit 5000;

Даже после исправления индексов план запроса не индексирует для сортировки.

 QUERY PLAN
Limit  (cost=3785.11..3785.22 rows=44 width=1767) (actual time=163.554..173.135 rows=5000 loops=1)
  ->  Sort  (cost=3785.11..3785.22 rows=44 width=1767) (actual time=163.553..172.791 rows=5000 loops=1)
"        Sort Key: l.""learnerUserName"", l.""learnerEmailId"""
        Sort Method: external merge  Disk: 35416kB
        ->  Nested Loop  (cost=1.12..3783.91 rows=44 width=1767) (actual time=0.019..63.743 rows=21195 loops=1)
              ->  Index Scan using primary_index__entity_learners_basic on entity_learners_basic elb  (cost=0.57..1109.79 rows=314 width=186) (actual time=0.010..6.221 rows=21195 loops=1)
                    Index Cond: (("companyId" = '909666665757230431'::bigint) AND ("gameId" = '1050403501267716928'::bigint))
              ->  Index Scan using "temp_learners_companyId_userId_idx" on temp_learners l  (cost=0.55..8.51 rows=1 width=1581) (actual time=0.002..0.002 rows=1 loops=21195)
                    Index Cond: (("companyId" = '909666665757230431'::bigint) AND ("userId" = elb."userId"))
Planning time: 0.309 ms
Execution time: 178.422 ms

Не использует ли Postgres индексы при объединении и упорядочивании данных?

1. Привет @jjanes запрос, который я опубликовал выше, является примером запросов, запрос, который мне нужно оптимизировать, является последним, к которому я присоединяюсь по идентификатору компании x идентификатору пользователя. Чтобы создать такое же поведение, я переписал приведенный выше запрос.

2. Найдено решение? Столкнувшись с чем-то подобным

Ответ №1:

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

Если вы хотите заставить PostgreSQL поверить, что сортировка — это худшая вещь в мире, вы могли бы set enable_sort=off . Если после этого он все еще выполняет сортировку, то вы знаете, что PostgreSQL не имеет правильных индексов, чтобы избежать сортировки, а не просто думать, что они на самом деле не будут быстрее.

Ответ №2:

PostgreSQL может использовать индекс на ("companyId", "learnerUserName", "learnerEmailId") для вашего первого запроса, но дополнительное IN условие уменьшает количество строк результата примерно до 4 строк, что означает, что сортировка вообще ничего не будет стоить. Поэтому он выбирает индекс, который может поддерживать IN условие.

Строки, возвращаемые с этим индексом, автоматически не будут расположены в правильном порядке, потому что

вы указали DESC для последнего столбца индекса, но ASC для предыдущего
в IN списке более одного элемента.

Без IN условия возвращается достаточное количество строк, поэтому PostgreSQL считает, что дешевле упорядочивать по индексу и отфильтровывать строки, которые не соответствуют условию.

С вашим первым запросом невозможно иметь индекс, который поддерживает как IN список в WHERE условии, так и в ORDER BY предложении, поэтому PostgreSQL должен сделать выбор.