Message-ID: <42ACFEEE.2050005@alteeve.com>
Date: Sun, 12 Jun 2005 23:35:10 -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: Bruno Wolff III <bruno@wolff.to>
Subject: Re: Index ot being used
References: <1825.216.209.137.1.1118419819.squirrel@mail.alteeve.com>
	<20050610155535.GO8451@tobias.nordicbet.com>
	<42AC42CB.8050703@alteeve.com> <24636.1118591783@sss.pgh.pa.us>
	<42ACBC95.9020404@alteeve.com> <20050613030001.GB17491@wolff.to>
In-Reply-To: <20050613030001.GB17491@wolff.to>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Bruno Wolff III wrote:
> On Sun, Jun 12, 2005 at 18:52:05 -0400,
>   Madison Kelly <linux@alteeve.com> wrote:
> 
>>  After sending that email I kept plucking away and in the course of 
>>doing so decided that I didn't need to return the 'file_type' column. 
>>Other than that, it would see my query now matches what you two have 
>>recommended in the 'ORDER BY...' front but I still can't get an index 
>>search.
> 
> 
> No it doesn't. Even if you don't return file_type you still need it
> in the order by clause if you want postgres to consider using your
> index.
> 
> Is there some reason you didn't actually try out our suggestion, but are
> now asking for more advice?

No good excuse.

I'll recreate the index and test out your suggestion...

tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type 
FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC, 
file_parent_dir ASC, file_name ASC;
                                                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=14789.92..14857.06 rows=26856 width=117) (actual 
time=16865.473..16989.104 rows=25795 loops=1)
    Sort Key: file_type, file_parent_dir, file_name
    ->  Seq Scan on file_info_7  (cost=0.00..11762.44 rows=26856 
width=117) (actual time=0.178..1920.413 rows=25795 loops=1)
          Filter: ((file_type)::text = 'd'::text)
  Total runtime: 17102.925 ms
(5 rows)

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"

   I'm still getting the sequential scan.

Madison


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