Nested loop in simple query taking long time
От | Henrik Zagerholm |
---|---|
Тема | Nested loop in simple query taking long time |
Дата | |
Msg-id | D1D68A03-63B8-47C0-8468-50CDC11B2856@mac.se обсуждение исходный текст |
Ответы |
Re: Nested loop in simple query taking long time
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: "Peter Childs"Дата:
Сообщение: Re: Server crashed and now experiencing slow running queries