From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Arup Rakshit <ar(at)zeit(dot)io>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Why my query not doing index only scan |
Date: | 2018-09-10 13:12:10 |
Message-ID: | e498902f3df994d43055baa489ebad291b591172.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Csaba Ragasits | 2018-09-10 13:43:49 | Re: PG8.3->10 migration data differences |
Previous Message | Stephen Frost | 2018-09-10 12:53:22 | Re: Why my query not doing index only scan |