CODEWITHSUNDEEP
postgresql
meotimdihia
meotimdihia

Reputation: 4299

How to index Postgresql query with multi-column and order

This query take 2s to done: 

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

But this query just take < 0.1s:

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

I have indexes on both colum countryCode and deletedAt

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

Upvotes: 1

Views: 83

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

I would create filtered/parial index:

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

Upvotes: 1

Related Questions