Moving this over to -devel after my original post on users. On Jul 20, 2011, at 10:40 PM, Dan Langille wrote:
> On Jul 19, 2011, at 5:45 PM, Roy Sigurd Karlsbakk wrote: > >>> Starting a file-based restore job, this led to postgresql spending a >>> full 15 minutes in a query. Any idea what can be done to fix this? >>> >>> http://paste.ubuntu.com/647576/ has a full explain/analyze of the >>> query, but so far, I haven't figured out where the problem is. Anyone >>> with an idea of how to tune this? >> >> create index file_filenameid on file(filenameid); Devs: any objection to making this permanent on at least the PostgreSQL catalog? There is really no downside to this. >> >> I guess that should be part of the FAQ... > > FYI, it's on my file: > > $ psql bacula > psql (8.4.7) > Type "help" for help. > > bacula=# \d file > Table "public.file" > Column | Type | Modifiers > ------------+---------+------------------------------------------------------- > fileid | bigint | not null default nextval('file_fileid_seq'::regclass) > fileindex | integer | not null default 0 > jobid | integer | not null > pathid | integer | not null > markid | integer | not null default 0 > lstat | text | not null > md5 | text | not null > filenameid | integer | not null > Indexes: > "file_pkey" PRIMARY KEY, btree (fileid) > "file_filenameid_idx" btree (filenameid) > "file_jobid_idx" btree (jobid) > "file_jpfid_idx" btree (jobid, pathid, filenameid) > "file_pathid" btree (pathid) > "file_pathid_idx" btree (pathid) > "testing" btree (fileid) > > bacula=# > > bacula=# select * from version ; > versionid > ----------- > 12 > (1 row) > > > $ bconsole > Connecting to Director bacula.example.org:9101 > 1000 OK: bacula-dir Version: 5.0.3 (04 August 2010) > Enter a period to cancel a command. > > > For what it's worth, I found this in make_postgresql_tables > > -- > -- Possibly add one or more of the following indexes > -- if your Verifies are too slow. > -- > -- CREATE INDEX file_pathid_idx on file(pathid); > -- CREATE INDEX file_filenameid_idx on file(filenameid); > > Moving this over to dev on my next reply. > > > -- > Dan Langille - http://langille.org > > > ------------------------------------------------------------------------------ > 5 Ways to Improve & Secure Unified Communications > Unified Communications promises greater efficiencies for business. UC can > improve internal communications as well as offer faster, more efficient ways > to interact with customers and streamline customer service. Learn more! > http://www.accelacomm.com/jaw/sfnl/114/51426253/ > _______________________________________________ > Bacula-users mailing list > bacula-us...@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users -- Dan Langille - http://langille.org ------------------------------------------------------------------------------ 5 Ways to Improve & Secure Unified Communications Unified Communications promises greater efficiencies for business. UC can improve internal communications as well as offer faster, more efficient ways to interact with customers and streamline customer service. Learn more! http://www.accelacomm.com/jaw/sfnl/114/51426253/ _______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
