DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=dPV2SrZ4NpGIBva9jjSghbQsY3xjFhoy0KI1jk+r70ecVPTEBgAs/wh1u7/Xu7SVg8
	H6oY+qwvPa+9IyzdqjqLmAhDVzhlAuV1vZt5jedPB08NoC6GVsQlnb7ti3O+zZRN/0Qy
	p8cCAmt+55AshA0iZAX1Xmi8vC9H4HzSIrIHk=
Message-ID: <84f0acdb0808062014p29cf7482ied332225a0a92c63@mail.gmail.com>
Date: Wed, 6 Aug 2008 22:14:17 -0500
From: "Joshua Shanks" <jjshanks@gmail.com>
To: "Craig Ringer" <craig@postnewspapers.com.au>
Subject: Re: query planner not using the correct index
Cc: pgsql-performance@postgresql.org
In-Reply-To: <489A623A.4090400@postnewspapers.com.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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.