Why my query not doing index only scan

From: Arup Rakshit <ar(at)zeit(dot)io>
To: pgsql-general(at)postgresql(dot)org
Subject: Why my query not doing index only scan
Date: 2018-09-10 10:19:17
Message-ID: C7195132-E8C4-4914-B96D-F9A2F166C18A@zeit.io
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello All,

I am learning at this point how index works in DBMS. So I am looking for a very broad explanation to clear my basics.

I have an index defined "inspector_tool_idx4_1" UNIQUE, btree (company_id, item_code, deleted_at). Now I am using the *company_id* column in the where clause, and the selecting just the *item_code* field for all matching rows. I expected here the planner will do a index only scans. But it is doing bitmap index scan. Any idea what it is not doing what I expected it to do.

inspection_development=# explain analyze select item_code from inspector_tool_components where company_id = '7881ff2e-0557-4734-9da8-2d33072ff1ef';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on inspector_tool_components (cost=45.92..1360.48 rows=1226 width=8) (actual time=0.382..1.202 rows=1232 loops=1)
Recheck Cond: (company_id = '7881ff2e-0557-4734-9da8-2d33072ff1ef'::uuid)
Heap Blocks: exact=81
-> Bitmap Index Scan on inspector_tool_idx4_1 (cost=0.00..45.61 rows=1226 width=0) (actual time=0.347..0.347 rows=1232 loops=1)
Index Cond: (company_id = '7881ff2e-0557-4734-9da8-2d33072ff1ef'::uuid)
Planning time: 0.207 ms
Execution time: 1.358 ms
(7 rows)

Thanks,

Arup Rakshit
ar(at)zeit(dot)io

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2018-09-10 11:28:17 Re: Why my query not doing index only scan
Previous Message Peter J. Holzer 2018-09-10 10:00:40 Re: PG8.3->10 migration data differences