On 20 Sep 2006 at 13:23, Bill Moran wrote:
> 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.
If I can get access to the databases in question, I know I can get
speed ouf ot it.
--
Dan Langille : Software Developer looking for work
my resume: http://www.freebsddiary.org/dan_langille.php
-------------------------------------------------------------------------
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
