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 ?
