Re: Why my query not doing index only scan

Arup Rakshit wrote:
> 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
matchingrows.
 
> I expected here the planner will do a index only scans. But it is doing bitmap index scan. Any idea what it is not
doingwhat 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=1232loops=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=1232loops=1)
 
>          Index Cond: (company_id = '7881ff2e-0557-4734-9da8-2d33072ff1ef'::uuid)
>  Planning time: 0.207 ms
>  Execution time: 1.358 ms
> (7 rows)

Try to "VACUUM (ANALYZE) inspector_tool_components", that will set the
visibility mape and get the statistics right, maybe than you get an
index only scan.

How many rows does the table contain?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com