Re: Why my query not doing index only scan - Mailing list pgsql-general

From Stephen Frost
Subject Re: Why my query not doing index only scan
Date
Msg-id 20180910112817.GW4184@tamriel.snowman.net
Whole thread Raw
In response to Why my query not doing index only scan  (Arup Rakshit <ar@zeit.io>)
Responses Re: Why my query not doing index only scan
Re: Why my query not doing index only scan
List pgsql-general
Greetings,

* Arup Rakshit (ar@zeit.io) 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
herethe planner will do a index only scans. But it is doing bitmap index scan. Any idea what it is not doing what I
expectedit 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

pgsql-general by date:

Previous
From: Arup Rakshit
Date:
Subject: Why my query not doing index only scan
Next
From: Arup Rakshit
Date:
Subject: Re: Why my query not doing index only scan