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
Indexes:
"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
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---------------------------(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
