Message-ID: <42ADC90B.4040808@alteeve.com>
Date: Mon, 13 Jun 2005 13:57:31 -0400
From: Madison Kelly <linux@alteeve.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.7.8) Gecko/20050513 Debian/1.7.8-1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>, tgl@sss.pgh.pa.us,
	tobias@nordicbet.com
Subject: Re: Index ot being used
References: <s2ad532e.099@gwmta.wicourts.gov>
In-Reply-To: <s2ad532e.099@gwmta.wicourts.gov>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Kevin Grittner wrote:
>>tle-bu=> \d file_info_7_display_idx
>>  Index "public.file_info_7_display_idx"
>>      Column      |         Type
>>-----------------+----------------------
>>  file_type       | character varying(2)
>>  file_parent_dir | text
>>  file_name       | text
>>btree, for table "public.file_info_7"
> 
> 
> The index is fine, but you need to phrase the query as
> 
> 	... ORDER BY file_type, file_parent_dir, file_name;
> 
> (Whether you use ASC or not doesn't matter.)  Otherwise the planner
> won't make the connection to the sort ordering of the index.
> 
> 			regards, tom lane

With Bruno's help I've gone back and tried just this with no luck. I've 
re-written the query to include all three items in the 'ORDER BY...' 
column in the same order but the sort still takes a long time and a 
sequential scan is being done instead of using the index.

For what it's worth, and being somewhat of a n00b, I agree with the idea 
of a smarter, more flexible planner. I guess the trade off is the added 
overhead neaded versus the size of the average query.

Madison

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-