B-Tree index not being used
От | Madison Kelly |
---|---|
Тема | B-Tree index not being used |
Дата | |
Msg-id | 42C61537.3080601@alteeve.com обсуждение исходный текст |
Ответы |
Re: B-Tree index not being used
|
Список | 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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
В списке pgsql-performance по дате отправления: