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

Another index question

From: Madison Kelly <linux(at)alteeve(dot)com>
To: pgsqlperform <pgsql-performance(at)postgresql(dot)org>
Subject: Another index question
Date: 2005-07-22 14:46:42
Message-ID: 42E106D2.5080604@alteeve.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi all,

   I am trying to do an update on a table but so far I can't seem to 
come up with a usable index. After my last question/thread the user 
'PFC' recommended I store whether a file was to be backed up as either 
't'(rue), 'f'(alse) or 'i'(nherit) to speed up changing files and sub 
directories under a given directory when it was toggled. I've more or 
less finished implementing this and it is certainly a LOT faster but I 
am hoping to make it just a little faster still with an Index.

   Tom Lane pointed out to me that I needed 'text_pattern_ops' on my 
'file_parent_dir' column in the index if I wanted to do pattern matching 
(the C locale wasn't set). Now I have added an additional condition and 
I think this might be my problem. Here is a sample query I am trying to 
create my index for:


UPDATE file_info_2 SET file_backup='i' WHERE file_backup!='i' AND 
file_parent_dir='/';

   This would be an example of someone changing the backup state of the 
root of a partition. It could also be:


UPDATE file_info_2 SET file_backup='i' WHERE file_backup!='i' AND 
file_parent_dir='/usr';

   If, for example, the user was toggling the backup state of the '/usr' 
directory.

   I suspected that because I was using "file_backup!='i'" that maybe I 
was running into the same problem as before so I tried creating the index:


tle-bu=> CREATE INDEX file_info_2_mupdate_idx ON file_info_2 
(file_backup bpchar_pattern_ops, file_parent_dir text_pattern_ops);

tle-bu=> EXPLAIN ANALYZE UPDATE file_info_2 SET file_backup='i' WHERE 
file_backup!='i' AND file_parent_dir~'^/'; 
                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
  Seq Scan on file_info_2  (cost=0.00..13379.38 rows=1 width=134) 
(actual time=1623.819..1624.087 rows=4 loops=1)
    Filter: ((file_backup <> 'i'::bpchar) AND (file_parent_dir ~ 
'^/'::text))
  Total runtime: 1628.053 ms
(3 rows)


   This index wasn't used though, even when I set 'enable_seqscan' to 
'OFF'. The column 'file_backup' is 'char(1)' and the column 
'file_parent_dir' is 'text'.


tle-bu=> \d file_info_2; \di file_info_2_mupdate_idx; 
Table "public.file_info_2"
      Column      |     Type     |          Modifiers
-----------------+--------------+------------------------------
  file_group_name | text         |
  file_group_uid  | integer      | not null
  file_mod_time   | bigint       | not null
  file_name       | text         | not null
  file_parent_dir | text         | not null
  file_perm       | integer      | not null
  file_size       | bigint       | not null
  file_type       | character(1) | not null
  file_user_name  | text         |
  file_user_uid   | integer      | not null
  file_backup     | character(1) | not null default 'i'::bpchar
  file_display    | character(1) | not null default 'i'::bpchar
  file_restore    | character(1) | not null default 'i'::bpchar
Indexes:
     "file_info_2_mupdate_idx" btree (file_backup bpchar_pattern_ops, 
file_parent_dir text_pattern_ops)
     "file_info_2_supdate_idx" btree (file_parent_dir, file_name, file_type)

                         List of relations
  Schema |          Name           | Type  |  Owner  |    Table
--------+-------------------------+-------+---------+-------------
  public | file_info_2_mupdate_idx | index | madison | file_info_2
(1 row)

   Could it be that there needs to be a certain number of 
"file_backup!='i'" before the planner will use the index? I have also 
tried not defining an op_class on both tables (and one at a time) but I 
can't seem to figure this out.

   As always, thank you!

Madison

Responses

pgsql-performance by date

Next:From: Sam MasonDate: 2005-07-22 14:51:22
Subject: Re: Planner doesn't look at LIMIT?
Previous:From: Tom LaneDate: 2005-07-22 14:39:57
Subject: Re: Planner doesn't look at LIMIT?

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