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 ? |
