Query taking too long. Problem reading explain output. - Mailing list pgsql-performance

Hello list,

I have a little query that takes too long and what I can see in  the
explain output is a seq scan on my biggest table
( tbl_file_structure) which I can't explain why.

Here is the output. I hope this is formatted correctly. If not, let
me know and I'll paste it somewhere.

Postgres version is 8.2.4 running on a Linux system with 2GB RAM and
a Core 2 Duo processor.


HashAggregate  (cost=418833.59..418833.63 rows=4 width=127) (actual
time=16331.326..16331.449 rows=160 loops=1)
    ->  Hash Left Join  (cost=16290.37..418833.51 rows=4 width=127)
(actual time=4386.574..16330.727 rows=160 loops=1)
          Hash Cond: (tbl_job.fk_job_group_id =
tbl_job_group.pk_job_group_id)
          Filter: ((tbl_job_group.job_group_type = 'B'::bpchar) OR
(tbl_job_group.job_group_type IS NULL))
          ->  Merge Join  (cost=16285.22..418828.08 rows=17
width=135) (actual time=4386.474..16330.253 rows=160 loops=1)
                Merge Cond: (tbl_computer.pk_computer_id =
tbl_share.fk_computer_id)
                ->  Nested Loop  (cost=16268.52..418810.55 rows=216
width=122) (actual time=4386.324..16329.638 rows=160 loops=1)
                      ->  Index Scan using tbl_computer_pkey on
tbl_computer  (cost=0.00..12.48 rows=1 width=20) (actual
time=0.013..0.024 rows=1 loops=1)
                            Filter: ((computer_name)::text =
'SOLARIS2'::text)
                      ->  Hash Join  (cost=16268.52..418795.91
rows=216 width=102) (actual time=4386.307..16329.425 rows=160 loops=1)
                            Hash Cond: (tbl_file.fk_filetype_id =
tbl_filetype.pk_filetype_id)
                            ->  Hash Join  (cost=16267.03..418791.44
rows=216 width=100) (actual time=4386.268..16329.119 rows=160 loops=1)
                                  Hash Cond:
(tbl_file_structure.fk_structure_id = tbl_structure.pk_structure_id)
                                  ->  Hash Join
(cost=8605.68..410913.87 rows=19028 width=40) (actual
time=22.810..16196.414 rows=17926 loops=1)
                                        Hash Cond:
(tbl_file_structure.fk_file_id = tbl_file.pk_file_id)
                                        ->  Seq Scan on
tbl_file_structure  (cost=0.00..319157.94 rows=16591994 width=16)
(actual time=0.016..7979.083 rows=16591994 loops=1)
                                        ->  Hash
(cost=8573.62..8573.62 rows=2565 width=40) (actual
time=22.529..22.529 rows=2221 loops=1)
                                              ->  Bitmap Heap Scan on
tbl_file  (cost=74.93..8573.62 rows=2565 width=40) (actual
time=1.597..20.691 rows=2221 loops=1)
                                                    Filter: (lower
((file_name)::text) ~~ 'index.php%'::text)
                                                    ->  Bitmap Index
Scan on tbl_file_idx  (cost=0.00..74.28 rows=2565 width=0) (actual
time=1.118..1.118 rows=2221 loops=1)
                                                          Index Cond:
((lower((file_name)::text) ~>=~ 'index.php'::character varying) AND
(lower((file_name)::text) ~<~ 'index.phq'::character varying))
                                  ->  Hash  (cost=7487.57..7487.57
rows=13902 width=76) (actual time=100.905..100.905 rows=24571 loops=1)
                                        ->  Index Scan using
tbl_structure_idx3 on tbl_structure  (cost=0.00..7487.57 rows=13902
width=76) (actual time=0.055..79.301 rows=24571 loops=1)
                                              Index Cond:
(fk_archive_id = 56)
                            ->  Hash  (cost=1.22..1.22 rows=22
width=18) (actual time=0.032..0.032 rows=22 loops=1)
                                  ->  Seq Scan on tbl_filetype
(cost=0.00..1.22 rows=22 width=18) (actual time=0.004..0.016 rows=22
loops=1)
                ->  Sort  (cost=16.70..16.70 rows=1 width=37) (actual
time=0.144..0.239 rows=1 loops=1)
                      Sort Key: tbl_share.fk_computer_id
                      ->  Nested Loop  (cost=4.26..16.69 rows=1
width=37) (actual time=0.072..0.115 rows=1 loops=1)
                            Join Filter: (tbl_share.pk_share_id =
tbl_archive.fk_share_id)
                            ->  Nested Loop Left Join
(cost=4.26..15.42 rows=1 width=24) (actual time=0.055..0.097 rows=1
loops=1)
                                  Join Filter: (tbl_archive.fk_job_id
= tbl_job.pk_job_id)
                                  ->  Bitmap Heap Scan on
tbl_archive  (cost=4.26..8.27 rows=1 width=24) (actual
time=0.033..0.033 rows=1 loops=1)
                                        Recheck Cond: (56 =
pk_archive_id)
                                        Filter: archive_complete
                                        ->  Bitmap Index Scan on
tbl_archive_pkey  (cost=0.00..4.26 rows=1 width=0) (actual
time=0.026..0.026 rows=1 loops=1)
                                              Index Cond: (56 =
pk_archive_id)
                                  ->  Seq Scan on tbl_job
(cost=0.00..6.51 rows=51 width=16) (actual time=0.003..0.033 rows=51
loops=1)
                            ->  Seq Scan on tbl_share
(cost=0.00..1.12 rows=12 width=29) (actual time=0.003..0.008 rows=12
loops=1)
          ->  Hash  (cost=4.51..4.51 rows=51 width=13) (actual
time=0.084..0.084 rows=51 loops=1)
                ->  Seq Scan on tbl_job_group  (cost=0.00..4.51
rows=51 width=13) (actual time=0.006..0.046 rows=51 loops=1)
  Total runtime: 16331.890 ms
(42 rows)

Here is the query if needed.
explain analyze SELECT file_name FROM tbl_file_structure JOIN
tbl_file ON pk_file_id = fk_file_id JOIN tbl_structure ON
pk_structure_id = fk_structure_id JOIN tbl_archive ON pk_archive_id
=fk_archive_id JOIN tbl_share ON pk_share_id =fk_share_id JOIN
tbl_computer ON pk_computer_id = fk_computer_id JOIN tbl_filetype ON
pk_filetype_id = fk_filetype_id LEFT OUTER JOIN tbl_job ON
tbl_archive.fk_job_id = pk_job_id LEFT OUTER JOIN tbl_job_group ON
tbl_job.fk_job_group_id = pk_job_group_id WHERE LOWER(file_name) LIKE
LOWER('index.php%') AND (computer_name = 'SOLARIS2') AND
(fk_archive_id = 56) AND archive_complete = true  AND (job_group_type
= 'B' OR job_group_type IS NULL) GROUP BY file_name, file_ctime,
structure_path, pk_computer_id, filetype_icon, computer_name,
share_name, share_path;

Thanks,
Henrik