In response to Frank Sweetser <[EMAIL PROTECTED]>:
> On Wed, Sep 20, 2006 at 09:44:24AM -0400, Bill Moran wrote:
> > Try them out ... if they make a significant improvement, use them.
>
> As an aid to experimenting with indexes, and to help people give more hard
> data, perhaps it might be usefull to add an option to dbcheck to output the
> results of running explain (mysql, I assume postgresql has an equivalent) on
> the queries used. This info is really useful to find hotspots that would
> benefit the most from adding an index or two.
Well, I have statement logging and query duration logging turned on
on PostgreSQL on our production system. Running dbcheck shows a bunch
of intense queries, Here are some of the killers:
duration: 153056.519 ms statement: SELECT Filename.FilenameId,
File.FilenameId FROM Filename LEFT OUTER JOIN File ON
(Filename.FilenameId=File.FilenameId) WHERE File.FilenameId IS NULL
bacula=> explain SELECT Filename.FilenameId,File.FilenameId FROM
Filename LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId)
WHERE File.FilenameId IS NULL;
QUERY PLAN
---------------------------------------------------------------------------------------------
Merge Left Join (cost=0.00..824877.11 rows=506718 width=8)
Merge Cond: ("outer".filenameid = "inner".filenameid)
Filter: ("inner".filenameid IS NULL)
-> Index Scan using filename_pkey on filename (cost=0.00..23530.19
rows=506718 width=4)
-> Index Scan using file_fp_idx on file (cost=0.00..676093.57 rows=9918925
width=4)
(5 rows)
Notice that it's using existing indexes, it's the join that's rough.
This one is rough as well:
duration: 185632.013 ms statement: SELECT DISTINCT Path.PathId,
File.PathId FROM Path LEFT OUTER JOIN File ON (Path.PathId=File.PathId)
WHERE File.PathId IS NULL
QUERY PLAN
-------------------------------------------------------------------------------------------------
Unique (cost=1819887.33..1824163.31 rows=570131 width=8)
-> Sort (cost=1819887.33..1821312.65 rows=570131 width=8)
Sort Key: path.pathid, file.pathid
-> Merge Left Join (cost=1587495.88..1760367.99 rows=570131 width=8)
Merge Cond: ("outer".pathid = "inner".pathid)
Filter: ("inner".pathid IS NULL)
-> Index Scan using path_pkey on path (cost=0.00..22662.91
rows=570131 width=4)
-> Sort (cost=1587495.88..1612293.20 rows=9918925 width=4)
Sort Key: file.pathid
-> Seq Scan on file (cost=0.00..278755.25 rows=9918925
width=4)
The query plan on this seems to indicate that an index on pathid would
be helpful. It's interesting that I didn't see any measurable
improvement.
--
Bill Moran
Collaborative Fusion Inc.
****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
