Why my query not using index to sort?

From: Arup Rakshit <ar(at)zeit(dot)io>
To: pgsql-general(at)postgresql(dot)org
Subject: Why my query not using index to sort?
Date: 2018-09-28 12:44:25
Message-ID: D973243A-5DDC-4B20-A1DF-A0C14A487D2A@zeit.io
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
ar(at)zeit(dot)io

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ravi Krishna 2018-09-28 13:08:54 Re: Why my query not using index to sort?
Previous Message Achilleas Mantzios 2018-09-28 12:13:02 Re: Replication Issues