Re: Why my query not doing index only scan

Greetings,

* Arup Rakshit (ar(at)zeit(dot)io) wrote:
> 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?

The best way to get a prod-like environment in development is to use a
file-level backup tool, like pgBackRest. There's a few nice things that
does:

- Copies the database files at a physical level, making it much closer
to what prod is like
- Checks your database checksums (if you have them enabled, which
hopefully you do, see initdb -k)
- Verifies your backup/restore approach
- Provides the fastest way to perform a restore in the event of an issue
- Allows you to do point-in-time-recovery (PITR)

Using pg_dump/pg_restore takes a logical export of the database and then
imports it into a new PG cluster. Even if you do VACUUM ANALYZE after
doing pg_dump/restore, you're going to end up with (somewhat, at least)
different stats, all the tables will be completely packed (no bloat, no
space for new tuples to go into existing pages), all of the indexes will
be completely rebuilt and pristine, etc. In the end, it's pretty
different and while it'll behavior in a similar manner to prod in a lot
of ways, there'll be a lot of ways it doesn't too.

Thanks!

Stephen