Bruno Wolff III wrote:
On Sun, Jun 12, 2005 at 23:42:05 -0400,
  Madison Kelly <[EMAIL PROTECTED]> wrote:

As you probably saw in my last reply, I went back to the old index and tried the query you and Tom Lane recommended. Should this not have caught the index?

Probably, but there might be some other reason the planner thought it
was better to not use it. Using indexes is not always faster.

It would help to see your latest definition of the table and indexes,
the exact query you used and explain analyze output.

Okay, here's what I have at the moment:

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_parent_dir, file_name)

tle-bu=> \d file_info_7_display_idx
Index "public.file_info_7_display_idx"
     Column      | Type
 file_parent_dir | text
 file_name       | text
btree, for table "public.file_info_7"

tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC, file_name ASC;
                                                         QUERY PLAN
Sort (cost=15091.53..15165.29 rows=29502 width=114) (actual time=12834.933..12955.136 rows=25795 loops=1)
   Sort Key: file_parent_dir, file_name
-> Seq Scan on file_info_7 (cost=0.00..11762.44 rows=29502 width=114) (actual time=0.244..2533.388 rows=25795 loops=1)
         Filter: ((file_type)::text = 'd'::text)
 Total runtime: 13042.421 ms
(5 rows)

Since my last post I went back to a query closer to what I actually want. What is most important to me is that 'file_parent_dir, file_name, file_display' are returned and that the results are sorted by 'file_parent_dir, file_name' and the results are restricted to where 'file_info='d''.

Basically what I am trying to do is display a directory tree in a file browser. I had this working before but it was far, far too slow once the number of directories to display got much higher than 1,000. That is what 'file_display' is, by the way.

  Again, thank you!


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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match

Reply via email to