Re: Why my query not doing index only scan

Hello Stephen,

I would like to ask one more question related to this topic. When I take a dump from production, and restore it to development DB, what are the commands I generally need to run to dev deb quack close to production?

Thanks,

Arup Rakshit
ar(at)zeit(dot)io

> On 10-Sep-2018, at 4:58 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> Greetings,
>
> * Arup Rakshit (ar(at)zeit(dot)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 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