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 a...@zeit.io > On 10-Sep-2018, at 4:58 PM, Stephen Frost <sfr...@snowman.net> wrote: > > Greetings, > > * Arup Rakshit (a...@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 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
