From: Craig Ringer <craig@postnewspapers.com.au>
To: Joshua Shanks <jjshanks@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query planner not using the correct index
Date: Thu, 07 Aug 2008 11:24:37 +0800
Message-ID: <489A6AF5.50908@postnewspapers.com.au> (raw)
In-Reply-To: <84f0acdb0808062014p29cf7482ied332225a0a92c63@mail.gmail.com>
References: <84f0acdb0808061435q5ebb607cjc311f1eb4da841a@mail.gmail.com>
<489A623A.4090400@postnewspapers.com.au>
<84f0acdb0808062014p29cf7482ied332225a0a92c63@mail.gmail.com>
Joshua Shanks wrote:
>> - 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.
OK, that's interesting. There are ways to examine Pg's statistics on
columns, get an idea of which stats might be less than accurate, etc,
but I'm not really familiar enough with it all to give you any useful
advice on the details. I can make one suggestion in the vein of shotgun
throubleshooting, though:
Try altering the statistics targets on the tables of interest, or tweak
the default_statistics_target, then rerun VACUUM ANALYZE and re-test.
Maybe start with a stats target of 100 and see what happens.
--
Craig Ringer
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: craig@postnewspapers.com.au, jjshanks@gmail.com
Subject: Re: query planner not using the correct index
In-Reply-To: <489A6AF5.50908@postnewspapers.com.au>
* 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