Стоимость SORT замедляет мой запрос

PostgreSQL 7.4 (обновление да)

Так что в моем состоянии WHERE у меня есть это

AND CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text 
        OR "substring"(t."FieldID"::text, 0, 4) = '123'::text 
        OR "substring"(t."FieldID"::text, 0, 5) = '5555'::text 
        OR "substring"(t."FieldID"::text, 0, 6) = '44444'::text 
        OR "substring"(t."FieldID"::text, 0, 3) = '99'::text 
    THEN 1
    ELSE 0
END = 1

Альтернативный синтаксис, но без изменений в стоимости

AND CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text THEN 1
    WHEN "substring"(t."FieldID"::text, 0, 4) = '123'::text THEN 1
    WHEN "substring"(t."FieldID"::text, 0, 5) = '5555'::text THEN 1
    WHEN "substring"(t."FieldID"::text, 0, 6) = '44444'::text THEN 1
    WHEN "substring"(t."FieldID"::text, 0, 3) = '99'::text THEN 1    
    ELSE 0
END = 1

Ищите экономически эффективный способ ограничить результаты началом строки. Поэтому, если строка начинается с 01, 123, 5555, 44444 или 99, добавьте ее в набор результатов.

Какие-нибудь мысли?

Примечание: FieldID индексируется Просмотр данных объяснения, чтобы увидеть узкие места в запросе, когда добавление приведенного выше кода происходит, когда стоимость сортировки повышается и замедляет возврат набора данных / результатов.

Вывод из Объяснить:

Sort (cost=88716.84..88719.89 rows=822 width=64)

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

Источник

4 ответа

Решение

Если вы просто фильтруете по начальным символам, вы можете использовать like без проблем, и он просто будет использовать индекс.

AND (t."FieldID"::text LIKE '01%' OR 
     t."FieldID"::text LIKE '123%' OR 
     t."FieldID"::text LIKE '5555%' OR
     t."FieldID"::text LIKE '44444%' OR
     t."FieldID"::text LIKE '99%')
Источник

Вы можете получить некоторую тягу, определив индексы выражений, которые соответствуют запросу; что-то вроде

CREATE INDEX t_fieldid_prefix_3 ON t (("substring"("FieldID"::text, 0, 3)))
CREATE INDEX t_fieldid_prefix_4 ON t (("substring"("FieldID"::text, 0, 4)))
CREATE INDEX t_fieldid_prefix_5 ON t (("substring"("FieldID"::text, 0, 5)))
CREATE INDEX t_fieldid_prefix_6 ON t (("substring"("FieldID"::text, 0, 6)))

Если вы всегда ищете одни и те же префиксы, включите все это в индекс:

CREATE INDEX t_fieldid_prefix ON t((CASE
    WHEN "substring"("FieldID"::text, 0, 3) = '01'::text 
        OR "substring"("FieldID"::text, 0, 4) = '123'::text 
        OR "substring"("FieldID"::text, 0, 5) = '5555'::text 
        OR "substring"("FieldID"::text, 0, 6) = '44444'::text 
        OR "substring"("FieldID"::text, 0, 3) = '99'::text 
    THEN 1
    ELSE 0
END))
Источник

Я понятия не имею, поддерживается ли это вашей древней версией, но вы можете попытаться создать индекс для выражения сортировки, чтобы увидеть, улучшает ли это запрос:

CREATE INDEX idx_case ON the_table (
  (CASE
      WHEN substring("FieldID", 0, 3) = '01' THEN 1
      WHEN substring("FieldID", 0, 4) = '123' THEN 1
      WHEN substring("FieldID", 0, 5) = '5555' THEN 1
      WHEN substring("FieldID", 0, 6) = '44444' THEN 1
      WHEN substring("FieldID", 0, 3) = '99' THEN 1    
      ELSE 0
  END));

В текущей версии я уверен, что это можно использовать для улучшения шага ORDER BY

Источник

В зависимости от того, как часто выполняется запрос такого типа, а также от объема данных, вы можете рассчитать некоторые из этих внешних по отношению к запросу и добавление дополнительных столбцов для использования только в качестве индексов. Точно так же хранилище данных денормализуется для ускорения запросов отчетов.

Источник
Другие вопросы по тегам