Search Postgresql Archives

Re: Why my query not doing index only scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



On 10-Sep-2018, at 4:58 PM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote:

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux