From 8e7a742d1b1d90703ba65256b48fd3681dffad04 Mon Sep 17 00:00:00 2001 From: Eric Bollengier Date: Tue, 22 Sep 2009 23:17:03 +0200 Subject: [PATCH] Remove the file_fp_idx index for Postgresql (FilenameId, PathId) on File table. It is useless for common usage, and causes performance issues. This index fragments over the time and the update cost grows very quickly. (This index is not present on Mysql schema) --- bacula/src/cats/make_postgresql_tables.in | 12 ++++++------ bacula/technotes | 6 ++++++ 2 files changed, 12 insertions(+), 6 deletions(-) diff --git a/bacula/src/cats/make_postgresql_tables.in b/bacula/src/cats/make_postgresql_tables.in index 3a09576406..f53d6f49a9 100644 --- a/bacula/src/cats/make_postgresql_tables.in +++ b/bacula/src/cats/make_postgresql_tables.in @@ -27,9 +27,6 @@ CREATE TABLE path ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000; CREATE UNIQUE INDEX path_name_idx on path (path); --- In File table --- FileIndex can be 0 for FT_DELETED files --- FileNameId can link to Filename.Name='' for directories CREATE TABLE file ( fileid bigserial not null, @@ -43,8 +40,12 @@ CREATE TABLE file primary key (fileid) ); -CREATE INDEX file_jobid_idx on file (jobid); -CREATE INDEX file_fp_idx on file (filenameid, pathid); +CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid); + +-- If you are running PostgreSQL >= 8.2, you can remove this index +-- the database engine is able to use the composite index +-- and it increase performances during insertion +CREATE INDEX file_jobid_idx on file(jobid); -- -- Add this if you have a good number of job @@ -57,7 +58,6 @@ CREATE INDEX file_fp_idx on file (filenameid, pathid); -- -- CREATE INDEX file_pathid_idx on file(pathid); -- CREATE INDEX file_filenameid_idx on file(filenameid); --- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid); CREATE TABLE job ( diff --git a/bacula/technotes b/bacula/technotes index 5634b0a8df..ff2ebb5f94 100644 --- a/bacula/technotes +++ b/bacula/technotes @@ -2,6 +2,12 @@ General: +22Sep09 +ebl Remove the file_fp_idx index for Postgresql (FilenameId, PathId) on + File table. + It is useless for common usage, and causes performance issues. + This index fragments over the time and the update cost grows + very quickly. (This index is not present on Mysql schema) 17Sep09 kes Fix seg fault in ignoredir code 15Sep09 -- 2.39.5