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
Следующее
От: Tomas
Дата:
Сообщение: pg_dump: could not format inet value