Hello list,
Usually I can see what is wrong with queries but I can't figure out
why this query is slow.
Below is query and explain analyze output.
Any help would be appreciated.
EXPLAIN ANALYZE SELECT computer_name
FROM tbl_computer
INNER JOIN tbl_share ON pk_computer_id = tbl_share.fk_computer_id
INNER JOIN tbl_archive ON pk_share_id = tbl_archive.fk_share_id
LEFT OUTER JOIN tbl_job ON fk_job_id = pk_job_id
LEFT OUTER JOIN tbl_job_group ON fk_job_group_id = pk_job_group_id
WHERE archive_complete IS TRUE AND (job_group_type != 'R' OR
job_group_type IS NULL)
GROUP BY computer_name ORDER BY computer_name;
Group (cost=1171.27..19099.89 rows=16 width=11) (actual
time=4184.019..40929.159 rows=14 loops=1)
-> Nested Loop Left Join (cost=1171.27..19099.61 rows=112
width=11) (actual time=4184.018..40928.994 rows=192 loops=1)
Join Filter: (tbl_archive.fk_job_id = tbl_job.pk_job_id)
Filter: ((tbl_job_group.job_group_type <> 'R'::bpchar) OR
(tbl_job_group.job_group_type IS NULL))
-> Nested Loop (cost=1151.65..18960.06 rows=123 width=19)
(actual time=4080.070..40821.217 rows=192 loops=1)
-> Nested Loop (cost=1.18..43.19 rows=16 width=19)
(actual time=59.167..91.739 rows=16 loops=1)
Join Filter: (tbl_computer.pk_computer_id =
tbl_share.fk_computer_id)
-> Index Scan using computer_name on
tbl_computer (cost=0.00..36.25 rows=16 width=19) (actual
time=48.844..81.210 rows=16 loops=1)
-> Materialize (cost=1.18..1.34 rows=16
width=16) (actual time=0.645..0.650 rows=16 loops=16)
-> Seq Scan on tbl_share (cost=0.00..1.16
rows=16 width=16) (actual time=10.312..10.318 rows=16 loops=1)
-> Bitmap Heap Scan on tbl_archive
(cost=1150.47..1182.20 rows=8 width=16) (actual
time=1564.082..2545.570 rows=12 loops=16)
Recheck Cond: (tbl_share.pk_share_id =
tbl_archive.fk_share_id)
Filter: (archive_complete IS TRUE)
-> Bitmap Index Scan on tbl_archive_idx1
(cost=0.00..1150.47 rows=8 width=0) (actual time=1505.456..1505.456
rows=86053 loops=16)
Index Cond: (tbl_share.pk_share_id =
tbl_archive.fk_share_id)
-> Materialize (cost=19.62..20.01 rows=39 width=13) (actual
time=0.385..0.549 rows=39 loops=192)
-> Hash Left Join (cost=11.65..19.58 rows=39
width=13) (actual time=73.747..103.889 rows=39 loops=1)
Hash Cond: (tbl_job.fk_job_group_id =
tbl_job_group.pk_job_group_id)
-> Seq Scan on tbl_job (cost=0.00..7.39 rows=39
width=16) (actual time=54.284..84.396 rows=39 loops=1)
-> Hash (cost=11.29..11.29 rows=29 width=13)
(actual time=0.119..0.119 rows=29 loops=1)
-> Seq Scan on tbl_job_group
(cost=0.00..11.29 rows=29 width=13) (actual time=0.013..0.087 rows=29
loops=1)
Total runtime: 40929.241 ms
Cheer,
Henke
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
