LINUX.ORG.RU
Форум — Development  

PostgreSQL Не работает Incremental Sort

 


0

2

PGSQL версия 14

Есть таблица Documents и таблица Search (в ней лежат данные для поиска по Documents).

Связь между таблицами Documents 1-> N Search. Таблицы связаны по полям RootId (ид. корня документа) и Id (идентификатор записи документа).

Есть один документ, где все названия записей начинаются со строки «1.2», записей таких 110K+.

При поиске по этой строке план запроса получается плохой, PG достает все записи и сортирует их, это занимает время и shared hit\shared read

   select 
      s."FullName", s."Id"
FROM
	 "Documents" s
inner join "Search" k on (k."RootId" = s."RootId" and k."Id" = s."Id")
where
		k."Name" is true AND
		k."Item" iLIKE '1.2%' AND
		k."RootId" = 111 
ORDER by
     s."FullName"
limit 40

но если из запроса убрать условие k.«Item» iLIKE ‘1.2%’

select 
      s."FullName", s."Id"
FROM
	 "Documents" s
inner join "Search" k on (k."RootId" = s."RootId" and k."Id" = s."Id")
where
		k."Name" is true AND
		k."RootId" = 111 
ORDER by
     s."FullName"
limit 40

то PG начинает использовать Incremental Sort, достает только 40 записей уже отсортированных и план становится намного лучше.

Вопрос: почему PG может не использовать Incremental Sort, и как помочь ему это сделать?



Последнее исправление: polin11 (всего исправлений: 2)
← Полнотекстовый поиск и кодирование Хаффмана
Как всё-таки получить буффер primary selection на Wayland? → 
k."Name" is true AND
k."Item" iLIKE '1.2%' AND
k."RootId" = 111

Масло - масленное. Не?

k.«RootId» = 111 он только 1 зачем все остальное или я что-то не понял.

alnkapa
()

смари, чо мне бездушные железки говорят!

ILIKE is case-insensitive and, by default, standard B-tree indexes cannot be used to optimize ILIKE queries effectively.

Solution/Workaround CREATE INDEX idx_search_item_lower ON «Search» (LOWER(«Item»));

and then rewrite your query to use the LOWER() function:

where
		k."Name" is true AND
		LOWER(k."Item") LIKE '1.2%' AND -- Use LIKE with lowercase pattern
		k."RootId" = 111

сам я всё это, естественно, не проверял, но такие вопросы давно уже не на лор надо писать… )

P.S.: из примера выше вообще не особо понятно, зачем там ilike.. ну, в смысле, зачем без учета регистра..

aol ★★★★★
()
Последнее исправление: aol (всего исправлений: 1)
Ответ на: комментарий от masa

я вообще заметил, что код оно пишет хуже, чем оптимизирует sql ))

aol ★★★★★
()
  1. нужно привести планы по крайней мере от explain (analyze), а еще лучше от explain( analyze, buffers)
  2. запрос сам по себе какой-то бессмысленный и есть сомнение, что это реальный запрос: оно посредством INNER JOIN умножает Documents на Search, а выбирает только из Documents, что несколько странно - там же в результате гора дублей вываливается, наверняка же вместо INNER JOIN нужен либо IN / EXISTS либо LATERAL с LIMIT 1, что суть одно и то же, но PostgreSQL может разные планы строить при этом
  3. «начинает использовать Incremental Sort, достает только 40 записей уже отсортированных и план становится намного лучше» - это заблуждение, оно просто начинает использовать совсем неподходящий индекс (подозреваю это ON Documents(FullName)) и из-за перекосов в БД и паразитного умножения как-то ухитряется довольно быстро набрать 40 строк
  4. с большой долей вероятности здесь запрос с LOOSE SCAN нужен
borisych ★★★★★
()
Последнее исправление: borisych (всего исправлений: 1)

Во-первых, зачем тебе именно ilike если у тебя паттерн «1.2%» без букв вообще, тебе наверное хватит like в этом месте.

Во-вторых, like для префиксов может использовать btree индекс.

В-третьих, если у тебя вдруг паттерн будет сложнее, то следует рассмотреть gin или gist индексы.

AnDoR ★★★★★
()
Для того чтобы оставить комментарий войдите или зарегистрируйтесь.
← Полнотекстовый поиск и кодирование Хаффмана
Development
Как всё-таки получить буффер primary selection на Wayland? →