Message-ID: <42AC42CB.8050703@alteeve.com>
Date: Sun, 12 Jun 2005 10:12:27 -0400
From: Madison Kelly <linux@alteeve.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.7.8) Gecko/20050513 Debian/1.7.8-1
MIME-Version: 1.0
To: Tobias Brox <tobias@nordicbet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index ot being used
References: <1825.216.209.137.1.1118419819.squirrel@mail.alteeve.com>
	<20050610155535.GO8451@tobias.nordicbet.com>
In-Reply-To: <20050610155535.GO8451@tobias.nordicbet.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Tobias Brox wrote:
> [linux@alteeve.com - Fri at 12:10:19PM -0400]
> 
>>tle-bu=> EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM
>>file_info_7;
>>                                        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
Indexes:
     "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

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-