How to index Postgresql query with multi-column and order

Question

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");

Lukasz Szozda · Accepted Answer · 2019-03-31 09:38:53Z

1

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);

edited Mar 31, 2019 at 9:38

answered Mar 31, 2019 at 9:24

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

meotimdihia Over a year ago

Not work for me, this is Explain result: explain.depesz.com/s/gpgJ

Lukasz Szozda Over a year ago

Let's try adding id:

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

meotimdihia Over a year ago

Thanks a lot, its work now :). Now it just takes 0.6ms explain.depesz.com/s/Zt7b

Lukasz Szozda Over a year ago

When you are creating covering index you should start with columns that are used for filtering i.e. country_coude, then deteledAt and finally id. I am not sure if PostgreSQL supports index skip column scan.

Laurenz Albe Over a year ago

You should open a new question for the other problem.

|

Collectives™ on Stack Overflow

How to index Postgresql query with multi-column and order

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related