Message-ID: <42C61537.3080601@alteeve.com>
Date: Sat, 02 Jul 2005 00:16:55 -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: pgsql-performance@postgresql.org
Subject: B-Tree index not being used
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi all,

   I have gone back to my index problem from a while ago where I am 
trying to do an update with a regex on the WHERE column. If I specifiy a 
constant the index is used so that much I know is working.

   I've been reading the 7.4 docs and I saw that a B-Tree index *should* 
but used when the regex is anchored to the start. This is from 11.2 of 
the docs; It says "The optimizer can also use a B-tree indexfor queries 
involving pattern matching operators LIKE, ILIKE, ~, and ~*, if, the 
pattern is anchored to the beginning of the string." In my case that is 
what I will always do.

   Specifically, this is a backup program I am using the DB for. The 
table I am working on stores all the file and directory information for 
a given partition. When the user toggles the checkbox for a given 
directory (to indicate that they do or do not what that directory backed 
up) I make a call to the DB telling it to change that column to given 
state.

   When the user toggle a directory I want to propgate that change to 
all sub directories and all files within those directories. The way I do 
this is:

UPDATE file_info_11 SET file_backup='t' WHERE file_parent_dir~'^/foo/bar';

   Which basically is just to say "change every directory and file with 
this parent directory and all sub directories to the new backup state". 
 From what I gather this query should have used the index. Here is what 
I am actually getting though:

tle-bu=> EXPLAIN ANALYZE UPDATE file_info_11 SET file_backup='t' WHERE 
file_parent_dir~'^/';
                                                       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
  Seq Scan on file_info_11  (cost=0.00..13484.23 rows=1 width=183) 
(actual time=13.560..22040.603 rows=336039 loops=1)
    Filter: (file_parent_dir ~ '^/'::text)
  Total runtime: 514099.565 ms
(3 rows)

   Now if I define a static directory the index IS used:

tle-bu=> EXPLAIN ANALYZE UPDATE file_info_11 SET file_backup='t' WHERE 
file_parent_dir='/';
                                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using file_info_11_update_idx on file_info_11 
(cost=0.00..109.69 rows=66 width=183) (actual time=22.828..62.020 rows=3 
loops=1)
    Index Cond: (file_parent_dir = '/'::text)
  Total runtime: 88.334 ms
(3 rows)

   Here is the table and index schemas:

tle-bu=> \d file_info_11; \d file_info_11_update_idx;
                               Table "public.file_info_11"
         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_11_display_idx" btree (file_type, file_parent_dir, 
file_name)
     "file_info_11_update_idx" btree (file_parent_dir)

Index "public.file_info_11_update_idx"
      Column      | Type
-----------------+------
  file_parent_dir | text
btree, for table "public.file_info_11"


   Can anyone see why the index might not be being used?

   I know that 'tsearch2' would probably work but it seems like way more 
than I need (because I will never be searching the middle of a string).

Thanks for any advice/help/pointers!

Madison

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