From: Joshua Shanks <jjshanks@gmail.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: query planner not using the correct index
Date: Wed, 6 Aug 2008 22:14:17 -0500
Message-ID: <84f0acdb0808062014p29cf7482ied332225a0a92c63@mail.gmail.com> (raw)
In-Reply-To: <489A623A.4090400@postnewspapers.com.au>
References: <84f0acdb0808061435q5ebb607cjc311f1eb4da841a@mail.gmail.com>
<489A623A.4090400@postnewspapers.com.au>
> - Have you changed the random page cost on either installation?
This is whatever the default is for both boxes (commented config file says 4.0)
> - Have both installations had VACUUM ANALYZE run recently?
This is the first thing I did and didn't seem to do anything.
Oddly enough I just went and did a VACUUM ANALYZE on a newly restored
db on the test server and get the same query plan as production so I
am now guessing something with the stats from ANALYZE are making
postgres think the string index is the best bet but is clearly 1000's
of times slower.
> - Are the stats targets the same on both installations?
If you mean default_statistics_target that is also the default
(commented config file says 10)
> - Do both installations have similar shared buffers, total available RAM info, etc?
The boxes have different configs as the test box isn't as big as the
production on so it doesn't have as much resources available or
allocated to it.
I did run the query on the backup db box (exact same hardware and
configuration as the production box) which gets restored from a backup
periodically (how I populated the test db) and got the same results as
the test box.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jjshanks@gmail.com, craig@postnewspapers.com.au
Subject: Re: query planner not using the correct index
In-Reply-To: <84f0acdb0808062014p29cf7482ied332225a0a92c63@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox