[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Bacula-devel] small mysql patch


Yuri Timofeev wrote:
> Hi
> 
> 2008/9/1 Dan Langille <dan@xxxxxxxxxxxx>:
>> If the index problem is just MySQL related, perhaps we should be
>> including these indexes with PostgreSQL.  FWIW, I seem to have these
>> indexes already:
>>
>> bacula=# \d file
>>                              Table "public.file"
>>    Column   |  Type   |                       Modifiers
>> ------------+---------+-------------------------------------------------------
>>  fileid     | integer | 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::bigint))
>>
> 
> 
> My postgresql does not want to use indexes.
> Could you check this?
> 
> bacula=# EXPLAIN SELECT File.FileId, Job.JobId FROM File LEFT OUTER JOIN Job ON
>   (File.JobId = Job.JobId) WHERE Job.JobId IS NULL LIMIT 300000;
> 
>                                 QUERY PLAN
> ---------------------------------------------------------------------------
>  Limit  (cost=78.86..24169.76 rows=300000 width=8)
>    ->  Hash Left Join  (cost=78.86..181966.90 rows=2265021 width=8)
>          Hash Cond: (file.jobid = job.jobid)
>          Filter: (job.jobid IS NULL)
>          ->  Seq Scan on file  (cost=0.00..113937.42 rows=4530042 width=8)
>          ->  Hash  (cost=58.38..58.38 rows=1638 width=4)
>                ->  Seq Scan on job  (cost=0.00..58.38 rows=1638 width=4)
> (7 rows)


bacula=# select count(*) from file;
   count
---------
  3532405
(1 row)

bacula=# select count(*) from job;
  count
-------
   8336
(1 row)

bacula=#


The output of this command is truncated... I resized the window and lost 
some of the text

bacula=# EXPLAIN SELECT File.FileId, Job.JobId FROM Fil
bacula-#   (File.JobId = Job.JobId) WHERE Job.JobId IS
                                 QUERY PLAN
-------------------------------------------------------
  Limit  (cost=413.79..45084.02 rows=300000 width=8)
    ->  Hash Left Join  (cost=413.79..251627.85 rows=168
          Hash Cond: (file.jobid = job.jobid)
          Filter: (job.jobid IS NULL)
          ->  Seq Scan on file  (cost=0.00..175293.48 ro
          ->  Hash  (cost=309.46..309.46 rows=8346 width
                ->  Seq Scan on job  (cost=0.00..309.46
(7 rows)

Once you get over a certain number of rows relative to the total number 
of rows in the table, there is no advantage to using the index.  It can 
be faster to not use the index under such circumstances.




bacula=# vacuum analyse job;
VACUUM
bacula=# EXPLAIN SELECT File.FileId, Job.JobId FROM File LEFT OUTER JOIN 
Job ON
   (File.JobId = Job.JobId) WHERE Job.JobId IS NULL LIMIT 300000;
                                 QUERY PLAN
---------------------------------------------------------------------------
  Limit  (cost=413.56..43956.85 rows=300000 width=8)
    ->  Hash Left Join  (cost=413.56..256767.72 rows=1766202 width=8)
          Hash Cond: (file.jobid = job.jobid)
          Filter: (job.jobid IS NULL)
          ->  Seq Scan on file  (cost=0.00..176875.05 rows=3532405 width=8)
          ->  Hash  (cost=309.36..309.36 rows=8336 width=4)
                ->  Seq Scan on job  (cost=0.00..309.36 rows=8336 width=4)
(7 rows)

bacula=#

Queries can be tuned.  Unfortunately, I ran dbcheck last night to see 
how long it takes, so I have no data to be returned from these queries.


There are several database engines settings (postgresql.conf) which can 
be adjusted to better suit particular needs.  PostgreSQL ships with a 
very minimalist configuration, by design.  Adjusting these tuning values 
can result in quite significant improvements.

> 
> 
> bacula=# \d file;
>                             Table "public.file"
>   Column | Type  |              Modifiers
> ----------------+---------+-------------------------------------------------------
>  fileid         | integer | not null default
> nextval('file_fileid_seq'::regclass)
>  fileindex      | integer | not null default 0
>  jobid          | integer | not null
>  pathid         | integer | not null
>  filenameid     | integer | not null
>  markid         | integer | not null default 0
>  lstat          | text    | not null
>  md5            | text    | not null
> Indexes:
>     "file_pkey" PRIMARY KEY, btree (fileid)
>     "file_fp_idx" btree (filenameid, pathid)
>     "file_jobid_idx" btree (jobid)




-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Bacula-devel mailing list
Bacula-devel@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-devel


This mailing list archive is a service of Copilotco.