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
