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