explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 1i1

options
# exclusive inclusive rows x rows loops node
1. 0.022 20206.130 ↑ 1.8 15 1
→

GroupAggregate (cost=17071.27..17071.37 rows=27 width=32) (actual time=20206.114..20206.130 rows=15 loops=1)

  • Output: a.version_title, count(*)
2. 0.159 20206.108 ↓ 4.5 121 1
→

Sort (cost=17071.27..17071.28 rows=27 width=32) (actual time=20206.102..20206.108 rows=121 loops=1)

  • Output: a.version_title
  • Sort Key: a.version_title
  • Sort Method: quicksort Memory: 30kB
3. 0.028 20205.949 ↓ 4.5 121 1
→

Subquery Scan a (cost=13644.24..17071.14 rows=27 width=32) (actual time=665.993..20205.949 rows=121 loops=1)

  • Output: a.version_title
4. 5249.393 20205.921 ↓ 4.5 121 1
→

GroupAggregate (cost=13644.24..17071.08 rows=27 width=35) (actual time=665.991..20205.921 rows=121 loops=1)

  • Output: l.database_name, max(v.version_title), sum(l.rpc_count)
5. 13978.770 14956.528 ↓ 9.9 6764518 1
→

Merge Join (cost=13644.24..16045.33 rows=683780 width=35) (actual time=542.612..14956.528 rows=6764518 loops=1)

  • Output: v.version_title, l.database_name, l.rpc_count
  • Merge Cond: ((h.database_name = l.database_name) AND (h.server_name = l.server_name))
6. 25.059 44.870 ↑ 1.0 6201 1
→

Sort (cost=1205.87..1209.03 rows=6310 width=35) (actual time=44.109..44.870 rows=6201 loops=1)

  • Output: v.version_title, h.database_name, h.server_name
  • Sort Key: h.database_name, h.server_name
  • Sort Method: quicksort Memory: 683kB
7. 11.375 19.811 ↑ 1.0 6201 1
→

Hash Join (cost=1.26..1126.22 rows=6310 width=35) (actual time=0.269..19.811 rows=6201 loops=1)

  • Output: v.version_title, h.database_name, h.server_name
  • Hash Cond: (h.script_number = v.db_version)
8. 8.405 8.405 ↓ 1.0 89684 1
→

Seq Scan on db_script_history h (cost=0.00..1000.29 rows=89644 width=29) (actual time=0.010..8.405 rows=89684 loops=1)

  • Output: h.db_script_history_id, h.server_name, h.database_name, h.script_number, h.date_executed
9. 0.013 0.031 ↑ 1.0 57 1
→

Hash (cost=1.11..1.11 rows=57 width=14) (actual time=0.031..0.031 rows=57 loops=1)

  • Output: v.version_title, v.db_version
10. 0.018 0.018 ↑ 1.0 57 1
→

Seq Scan on sp5_version v (cost=0.00..1.11 rows=57 width=14) (actual time=0.011..0.018 rows=57 loops=1)

  • Output: v.version_title, v.db_version
11. 867.165 932.888 ↓ 67.8 6768441 1
→

Sort (cost=12438.14..12488.07 rows=99867 width=34) (actual time=498.492..932.888 rows=6768441 loops=1)

  • Output: l.database_name, l.rpc_count, l.server_name
  • Sort Key: l.database_name, l.server_name
  • Sort Method: quicksort Memory: 16518kB
12. 45.034 65.723 ↓ 1.3 132781 1
→

Bitmap Heap Scan on sp5_log_part_sum l (cost=1243.04..10779.58 rows=99867 width=34) (actual time=22.048..65.723 rows=132781 loops=1)

  • Output: l.database_name, l.rpc_count, l.server_name
  • Recheck Cond: (date_logged > (now() - '3 mons'::interval))
13. 20.689 20.689 ↓ 1.4 136838 1
→

Bitmap Index Scan on idx_sp5_log_part_sum_date (cost=0.00..1238.05 rows=99867 width=0) (actual time=20.689..20.689 rows=136838 loops=1)

  • Index Cond: (date_logged > (now() - '3 mons'::interval))