Tobias Brox wrote:
[EMAIL PROTECTED] - Fri at 12:10:19PM -0400]

tle-bu=> EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM
                                       QUERY PLAN
Seq Scan on file_info_7  (cost=0.00..11028.35 rows=294035 width=118)
(actual time=0.122..2707.764 rows=294035 loops=1)
Total runtime: 3717.862 ms
(2 rows)

As far as I can see, you are selecting everything from the table without any
sort order.  The only rational thing to do then is a sequential scan, it's
no point in an index scan.

  Thanks for replying, Tobias and Jacques!

Doh! This is a case of over simplification, I think. I was trying to simplify my query as much as I could and then work it out to the actual query I want. It would seem I don't understand how to use indexes quite right. Do you think you might be able to help me with a useful index?

Here is the 'file_info_7' schema, my query and the 'explain analyze' results:

tle-bu=> \d file_info_7
                              Table "public.file_info_7"
        Column        |         Type         |                Modifiers
 file_group_name      | text                 |
 file_group_uid       | bigint               | not null
 file_mod_time        | bigint               | not null
 file_name            | text                 | not null
 file_parent_dir      | text                 | not null
 file_perm            | text                 | not null
 file_size            | bigint               | not null
file_type | character varying(2) | not null default 'f'::character varying
 file_user_name       | text                 |
 file_user_uid        | bigint               | not null
 file_backup          | boolean              | not null default true
 file_display         | boolean              | not null default false
 file_restore_display | boolean              | not null default false
 file_restore         | boolean              | not null default false
    "file_info_7_display_idx" btree (file_type, file_parent_dir, file_name)

  Here is my full query:

tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC;
                                                         QUERY PLAN
Sort (cost=14541.24..14603.48 rows=24895 width=118) (actual time=15751.804..15967.591 rows=25795 loops=1)
   Sort Key: file_parent_dir, file_name
-> Seq Scan on file_info_7 (cost=0.00..11763.44 rows=24895 width=118) (actual time=19.289..3840.845 rows=25795 loops=1)
         Filter: ((file_type)::text = 'd'::text)
 Total runtime: 16043.075 ms
(5 rows)

  This is my index (which I guess is wrong):

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"

Those are the three columns I am using in my restrictions so I thought that would create an index this query would use. Do I need to do something different because of the 'ORDER BY...'?

  Thanks again for the replies!


Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to