Блог

Главная - Вопросы по программированию - Как проиндексировать запрос Postgresql с помощью нескольких столбцов и порядка

Как проиндексировать запрос Postgresql с помощью нескольких столбцов и порядка

#postgresql

#postgresql

Вопрос:

Выполнение этого запроса занимает 2 секунды: 

 explain analyze select
    *
from
    "MdChapters" as "MdChapter" 
where
    ("MdChapter"."countryCode" = 'gb' AND "MdChapter"."deletedAt" is  null)
order by
    "MdChapter"."id" desc 
limit 100;
  
 Limit  (cost=56.35..56.38 rows=13 width=2656) (actual time=1854.038..1854.409 rows=100 loops=1)
  ->  Sort  (cost=56.35..56.38 rows=13 width=2656) (actual time=1854.035..1854.163 rows=100 loops=1)
        Sort Key: id DESC
        Sort Method: top-N heapsort  Memory: 150kB
        ->  Bitmap Heap Scan on "MdChapters" "MdChapter"  (cost=4.56..56.10 rows=13 width=2656) (actual time=49.818..1355.082 rows=327179 loops=1)
              Recheck Cond: ((("countryCode")::text = 'gb'::text) AND ("deletedAt" IS NULL))
              Heap Blocks: exact=47298
              ->  Bitmap Index Scan on test  (cost=0.00..4.55 rows=13 width=0) (actual time=42.630..42.632 rows=328948 loops=1)
                    Index Cond: ((("countryCode")::text = 'gb'::text) AND ("deletedAt" IS NULL))
Planning time: 0.200 ms
Execution time: 1854.567 ms

Но этот запрос просто занимает < 0,1 с:

 select
    *
from
    "MdChapters" as "MdChapter" 
where
    ("MdChapter"."countryCode" = 'gb')
order by
    "MdChapter"."id" desc 
limit 100;

У меня есть индексы как для столбца countryCode , так и deletedAt 

 CREATE INDEX md_chapters_deleted_at_country_code ON public."MdChapters" USING btree ("deletedAt", "countryCode");

Ответ №1:

Я бы создал отфильтрованный / parial индекс:

 CREATE INDEX md_chapters_deleted_at_country_code2 ON public."MdChapters" 
USING btree ("countryCode", "id")
WHERE ("deletedAt" IS NULL);

Комментарии:

1. У меня не работает, это объясняет результат: explain.depesz.com/s/gpgJ

2. Давайте попробуем добавить идентификатор: CREATE INDEX md_chapters_deleted_at_country_code2 ON public."MdChapters" USING btree ("countryCode", "id") WHERE ("deletedAt" IS NULL);

3. Большое спасибо, теперь это работает :). Теперь это занимает всего 0,6 мс explain.depesz.com/s/Zt7b

4. Когда вы создаете охватывающий индекс, вы должны начать со столбцов, которые используются для фильтрации, т.е. country_coude, затем deteledAt и, наконец, id. Я не уверен, поддерживает ли PostgreSQL сканирование столбца с пропуском индекса .

5. Вы должны открыть новый вопрос для другой проблемы.