Greetings, * Arup Rakshit (ar@xxxxxxx) wrote: > 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. One possibility is that the visibility map isn't current. Indexes don't include visibility information. The way an index-only scan works is that we track pages which are 'all visible' (meaning that every tuple on that page is visible to all running transactions) in a seperate file called the 'visibility map' (aka the VM). The VM is updated by the VACUUM process- but we only automatically run a VACUUM (with the autovacuum process) when thresholds have been reached for the number of UPDATE'd or DELETE'd tuples. What this means is that if you are playing around in development and just COPY a bunch of rows into a table, but don't ever UPDATE or DELETE the rows in that table, then you'll almost never get an index-only scan because the VM won't be current (and PG knows this). Make sure to do a VACUUM after loading data (and letting any ongoing transactions finish) and then re-test. That should make it sure that the VM is current and make it more likely that PG will do an index-only scan. Not a guarantee still, but that's the first thing I'd try, based on what you've shared here. Thanks! Stephen
Attachment:
signature.asc
Description: PGP signature
