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