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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
