From: Madison Kelly <linux@alteeve.com>
To: pgsqlperform <pgsql-performance@postgresql.org>
Subject: Another index question
Date: Fri, 22 Jul 2005 10:46:42 -0400
Message-ID: <42E106D2.5080604@alteeve.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: linux@alteeve.com
Subject: Re: Another index question
In-Reply-To: <42E106D2.5080604@alteeve.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox