Why my query not using index to sort?

[Arup Rakshit]

My query is not using name index to sort the result.

explain analyze select
        "vessels" .*
from
        "vessels"
where
        "vessels"."deleted_at" is null
        and "vessels"."company_id" = '86529964-6e9b-4bfa-ba9e-62bd24eaa954'
order by
        "vessels"."name" ASC;

I have below index in my vessels table:

Indexes:
    "vessels_pkey" PRIMARY KEY, btree (id)
    "inspector_tool_idx20_1" btree (company_id)
    "inspector_tool_idx20_2" btree (name)


and following is my plan output.

Sort  (cost=17.29..17.48 rows=76 width=107) (actual time=0.468..0.473 rows=77 
loops=1)
  Sort Key: name
  Sort Method: quicksort  Memory: 38kB
  ->  Bitmap Heap Scan on vessels  (cost=4.90..14.91 rows=76 width=107) (actual 
time=0.036..0.059 rows=77 loops=1)
        Recheck Cond: (company_id = 
'86529964-6e9b-4bfa-ba9e-62bd24eaa954'::uuid)
        Filter: (deleted_at IS NULL)
        Rows Removed by Filter: 4
        Heap Blocks: exact=3
        ->  Bitmap Index Scan on inspector_tool_idx20_1  (cost=0.00..4.88 
rows=81 width=0) (actual time=0.022..0.023 rows=81 loops=1)
              Index Cond: (company_id = 
'86529964-6e9b-4bfa-ba9e-62bd24eaa954'::uuid)
Planning time: 0.178 ms
Execution time: 0.527 ms

########################################################

After that I changed my index as follows, and the got almost same plan output.


Indexes:
    "vessels_pkey" PRIMARY KEY, btree (id)
    "inspector_tool_idx20_1" btree (company_id, name)

Sort  (cost=17.29..17.48 rows=76 width=107) (actual time=0.475..0.480 rows=77 
loops=1)
  Sort Key: name
  Sort Method: quicksort  Memory: 38kB
  ->  Bitmap Heap Scan on vessels  (cost=4.90..14.91 rows=76 width=107) (actual 
time=0.038..0.058 rows=77 loops=1)
        Recheck Cond: (company_id = 
'86529964-6e9b-4bfa-ba9e-62bd24eaa954'::uuid)
        Filter: (deleted_at IS NULL)
        Rows Removed by Filter: 4
        Heap Blocks: exact=3
        ->  Bitmap Index Scan on inspector_tool_idx20_1  (cost=0.00..4.88 
rows=81 width=0) (actual time=0.025..0.025 rows=81 loops=1)
              Index Cond: (company_id = 
'86529964-6e9b-4bfa-ba9e-62bd24eaa954'::uuid)
Planning time: 0.168 ms
Execution time: 0.533 ms

Is there anyway, I can improve the sorting so that it can use the index ?


Thanks,

Arup Rakshit
a...@zeit.io