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);
>
> 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-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
