Улучшения производительности запросов - фильтры сортировки

Question

Как можно повысить производительность этого запроса:

SELECT designs.* FROM designs
WHERE designs.state = 'in_stock'
ORDER BY designs.grade, id DESC

Объясните вывод:

 Sort  (cost=47475.35..47591.91 rows=46621 width=763)
   Sort Key: grade, id
   ->  Seq Scan on designs  (cost=0.00..12304.20 rows=46621 width=763)
         Filter: ((state)::text = 'in_stock'::text)

В таблице более 250000 записей.

Создание индекса на state не очень помогает

Answer 1

1) Назовите столбцы вместо *, такие как Select col1, col2.... Вам нужны все столбцы в результирующем наборе? Если не показывать только те столбцы, которые вам нужны.

2) Какие столбцы вы указали? Попробуйте индексировать по штату и включите оценку и ID.

3) Можно ли заменить state = 'in_stock ' чем-то, что фильтрует по целочисленному значению и дает тот же результат? Может ли само поле состояния быть преобразовано в тип int и проиндексировано, а затем создать таблицу поиска для этих целочисленных значений?

Answer 2

Чтобы оптимизировать данный запрос (что может быть не самой лучшей стратегией в целом), используйте частичный индекс с соответствующим порядком сортировки:

CREATE INDEX foo_idx ON designs (grade, id DESC) WHERE state = 'in_stock';

На самом деле я хотел бы начать с вашей таблицы, глядя на тип данных state первый.
Тогда я бы спросил, какие столбцы и строки вам действительно нужно вернуть из вашего запроса. Я сомневаюсь, что вам нужны все столбцы всех 46621 строк в результате для отображения?