pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedB-Tree index not being used
3+ messages / 2 participants
[nested] [flat]
* B-Tree index not being used
@ 2005-07-02 04:16 Madison Kelly <linux@alteeve.com>
0 siblings, 1 reply; 3+ messages in thread
From: Madison Kelly @ 2005-07-02 04:16 UTC (permalink / raw)
To: pgsql-performance
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: B-Tree index not being used
@ 2005-07-02 13:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Madison Kelly <linux@alteeve.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-07-02 13:54 UTC (permalink / raw)
To: Madison Kelly <linux@alteeve.com>; +Cc: pgsql-performance
Madison Kelly <linux@alteeve.com> writes:
> Can anyone see why the index might not be being used?
You didn't initdb in 'C' locale. You can either re-initdb,
or create a specialized index with a non-default operator class
to support LIKE. See the documentation.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: B-Tree index not being used
@ 2005-07-02 15:20 Madison Kelly <linux@alteeve.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Madison Kelly @ 2005-07-02 15:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
>
>> Can anyone see why the index might not be being used?
>
>
> You didn't initdb in 'C' locale. You can either re-initdb,
> or create a specialized index with a non-default operator class
> to support LIKE. See the documentation.
>
> regards, tom lane
I'll look into the non-default op class. I want to keep anything that
tweaks the DB in my code so that a user doesn't need to modify anything
on their system.
Thanks!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-07-02 15:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-02 04:16 B-Tree index not being used Madison Kelly <linux@alteeve.com>
2005-07-02 13:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-02 15:20 ` Madison Kelly <linux@alteeve.com>
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