Skip site navigation (1) Skip section navigation (2)

Re: Index ot being used

From: Madison Kelly <linux(at)alteeve(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Bruno Wolff III <bruno(at)wolff(dot)to>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Index ot being used
Date: 2005-06-13 04:29:08
Message-ID: 42AD0B94.9090508@alteeve.com (view raw or flat)
Thread:
Lists: pgsql-performance
Bruno Wolff III wrote:
> On Sun, Jun 12, 2005 at 23:42:05 -0400,
>   Madison Kelly <linux(at)alteeve(dot)com> 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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

In response to

Responses

pgsql-performance by date

Next:From: Yves VindevogelDate: 2005-06-13 06:54:21
Subject: View not using index
Previous:From: Bruno Wolff IIIDate: 2005-06-13 03:53:46
Subject: Re: Index ot being used

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group