Message-ID: <42E10C2C.4040202@alteeve.com>
Date: Fri, 22 Jul 2005 11:09:32 -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: pgsqlperform <pgsql-performance@postgresql.org>
Subject: Solved (was: Re: Another index question)
References: <42E106D2.5080604@alteeve.com>
In-Reply-To: <42E106D2.5080604@alteeve.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Line noise, sorry...

   After posting I went back to reading the pgsql docs and saw the query:


SELECT am.amname AS index_method, opc.opcname AS opclass_name, 
opr.oprname AS opclass_operator FROM pg_am am, pg_opclass opc, pg_amop 
amop, pg_operator opr WHERE opc.opcamid = am.oid AND amop.amopclaid = 
opc.oid AND amop.amopopr = opr.oid ORDER BY index_method, opclass_name, 
opclass_operator;

   Which listed all the op_classes. I noticed none of the 
opclass_operators supported '!=' so I wondered if that was simply an 
unindexable (is that a word?) operator. So I tried creating the index:


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

   And changing my query to:


tle-bu=> EXPLAIN ANALYZE UPDATE file_info_2 SET file_backup='i' WHERE 
file_backup='t' OR file_backup='f' AND file_parent_dir~'^/';
 
     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using file_info_2_mupdate_idx, file_info_2_mupdate_idx on 
file_info_2  (cost=0.00..10.04 rows=1 width=134) (actual 
time=0.112..0.718 rows=4 loops=1)
    Index Cond: ((file_backup = 't'::bpchar) OR ((file_backup = 
'f'::bpchar) AND (file_parent_dir ~>=~ '/'::text) AND (file_parent_dir 
~<~ '0'::text)))
    Filter: ((file_backup = 't'::bpchar) OR ((file_backup = 'f'::bpchar) 
AND (file_parent_dir ~ '^/'::text)))
  Total runtime: 60.359 ms
(4 rows)

   Bingo!

   Hopefully someone might find this useful in the archives. :p

Madison


Madison Kelly wrote:
> 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