explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cft

Settings
# exclusive inclusive rows x rows loops node
1. 114.826 5,871.415 ↓ 4.5 9 1

Nested Loop (cost=439,188.76..641,808.03 rows=2 width=16) (actual time=3,329.370..5,871.415 rows=9 loops=1)

  • Join Filter: ((cm_1.value)::text = (cm.value)::text)
  • Rows Removed by Join Filter: 292,322
2. 357.611 4,587.265 ↓ 8.6 292,331 1

Hash Join (cost=439,188.19..612,245.85 rows=34,170 width=31) (actual time=3,220.722..4,587.265 rows=292,331 loops=1)

  • Hash Cond: (c.from_companyaccountid = c_1.from_companyaccountid)
3. 1,031.506 1,031.506 ↑ 1.0 3,761,366 1

Seq Scan on connections c (cost=0.00..158,432.66 rows=3,808,879 width=32) (actual time=0.015..1,031.506 rows=3,761,366 loops=1)

  • Filter: ((kind)::text = 'e'::text)
  • Rows Removed by Filter: 1,992,941
4. 0.029 3,198.148 ↑ 1.0 100 1

Hash (cost=439,186.94..439,186.94 rows=100 width=31) (actual time=3,198.148..3,198.148 rows=100 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
5. 0.110 3,198.119 ↑ 1.0 100 1

Limit (cost=439,185.19..439,185.94 rows=100 width=31) (actual time=3,197.966..3,198.119 rows=100 loops=1)

6. 0.032 3,198.009 ↑ 101.0 100 1

Group (cost=439,185.19..439,260.98 rows=10,105 width=31) (actual time=3,197.965..3,198.009 rows=100 loops=1)

  • Group Key: c_1.from_companyaccountid, cm_1.value
7. 290.927 3,197.977 ↑ 99.1 102 1

Sort (cost=439,185.19..439,210.46 rows=10,105 width=31) (actual time=3,197.962..3,197.977 rows=102 loops=1)

  • Sort Key: c_1.from_companyaccountid, cm_1.value
  • Sort Method: quicksort Memory: 11,575kB
8. 68.888 2,907.050 ↓ 10.8 108,793 1

Nested Loop (cost=435,189.83..438,513.07 rows=10,105 width=31) (actual time=1,344.379..2,907.050 rows=108,793 loops=1)

9. 97.561 2,072.110 ↓ 287.2 109,436 1

Nested Loop (cost=435,189.27..438,149.78 rows=381 width=48) (actual time=1,344.354..2,072.110 rows=109,436 loops=1)

10. 75.080 1,375.044 ↓ 98.7 119,901 1

HashAggregate (cost=435,188.84..435,200.99 rows=1,215 width=16) (actual time=1,344.327..1,375.044 rows=119,901 loops=1)

  • Group Key: cm_2.connectionid
11. 1.370 1,299.964 ↓ 98.6 119,901 1

Gather (cost=1,000.00..435,185.80 rows=1,216 width=16) (actual time=10.752..1,299.964 rows=119,901 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 1,298.594 1,298.594 ↓ 78.8 39,967 3 / 3

Parallel Seq Scan on connectionmetadata cm_2 (cost=0.00..434,064.20 rows=507 width=16) (actual time=5.667..1,298.594 rows=39,967 loops=3)

  • Filter: (system AND ((key)::text = 'tags'::text) AND ((value)::text = 'CLOUDSCAN_AP_CONNECTION'::text))
  • Rows Removed by Filter: 8,255,697
13. 599.505 599.505 ↑ 1.0 1 119,901

Index Scan using connections_pkey on connections c_1 (cost=0.43..2.43 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=119,901)

  • Index Cond: (id = cm_2.connectionid)
  • Filter: ((NOT deleted) AND ((kind)::text = 'c'::text))
  • Rows Removed by Filter: 0
14. 766.052 766.052 ↑ 6.0 1 109,436

Index Only Scan using connectionmetadata_pkey on connectionmetadata cm_1 (cost=0.56..0.89 rows=6 width=31) (actual time=0.007..0.007 rows=1 loops=109,436)

  • Index Cond: ((connectionid = c_1.id) AND (key = 'AccountingSystemId'::text) AND (system = false))
  • Filter: (NOT system)
  • Heap Fetches: 41,406
15. 1,169.324 1,169.324 ↑ 6.0 1 292,331

Index Only Scan using connectionmetadata_pkey on connectionmetadata cm (cost=0.56..0.79 rows=6 width=31) (actual time=0.003..0.004 rows=1 loops=292,331)

  • Index Cond: ((connectionid = c.id) AND (key = 'AccountingSystemId'::text) AND (system = false))
  • Filter: (NOT system)
  • Heap Fetches: 841,091
Planning time : 2.254 ms
Execution time : 5,871.579 ms