explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7EIB

Settings
# exclusive inclusive rows x rows loops node
1. 0.123 7,192.826 ↓ 4.5 9 1

Nested Loop (cost=439,189.19..648,302.30 rows=2 width=16) (actual time=3,406.720..7,192.826 rows=9 loops=1)

2. 34.609 7,142.195 ↓ 40.7 122 1

Nested Loop (cost=439,188.76..648,300.93 rows=3 width=32) (actual time=3,312.592..7,142.195 rows=122 loops=1)

  • Join Filter: ((cm_1.value)::text = (cm.value)::text)
  • Rows Removed by Join Filter: 418,723
3. 957.407 5,006.121 ↓ 8.2 420,293 1

Hash Join (cost=439,188.19..605,318.15 rows=51,543 width=31) (actual time=3,272.197..5,006.121 rows=420,293 loops=1)

  • Hash Cond: (c.from_companyaccountid = c_1.from_companyaccountid)
4. 797.782 797.782 ↓ 1.0 5,754,313 1

Seq Scan on connections c (cost=0.00..144,068.93 rows=5,745,493 width=32) (actual time=0.009..797.782 rows=5,754,313 loops=1)

5. 0.025 3,250.932 ↑ 1.0 100 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
6. 0.114 3,250.907 ↑ 1.0 100 1

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

7. 0.033 3,250.793 ↑ 101.0 100 1

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

  • Group Key: c_1.from_companyaccountid, cm_1.value
8. 305.139 3,250.760 ↑ 99.1 102 1

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

  • Sort Key: c_1.from_companyaccountid, cm_1.value
  • Sort Method: quicksort Memory: 11,575kB
9. 76.642 2,945.621 ↓ 10.8 108,796 1

Nested Loop (cost=435,189.83..438,513.07 rows=10,105 width=31) (actual time=1,374.878..2,945.621 rows=108,796 loops=1)

10. 0.000 2,102.906 ↓ 287.2 109,439 1

Nested Loop (cost=435,189.27..438,149.78 rows=381 width=48) (actual time=1,374.842..2,102.906 rows=109,439 loops=1)

11. 76.462 1,404.009 ↓ 98.7 119,904 1

HashAggregate (cost=435,188.84..435,200.99 rows=1,215 width=16) (actual time=1,374.810..1,404.009 rows=119,904 loops=1)

  • Group Key: cm_2.connectionid
12. 0.000 1,327.547 ↓ 98.6 119,904 1

Gather (cost=1,000.00..435,185.80 rows=1,216 width=16) (actual time=7.231..1,327.547 rows=119,904 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 1,338.637 1,338.637 ↓ 78.8 39,968 3 / 3

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

  • Filter: (system AND ((key)::text = 'tags'::text) AND ((value)::text = 'CLOUDSCAN_AP_CONNECTION'::text))
  • Rows Removed by Filter: 8,255,703
14. 719.424 719.424 ↑ 1.0 1 119,904

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

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

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,439)

  • Index Cond: ((connectionid = c_1.id) AND (key = 'AccountingSystemId'::text) AND (system = false))
  • Filter: (NOT system)
  • Heap Fetches: 42,397
16. 2,101.465 2,101.465 ↑ 6.0 1 420,293

Index Only Scan using connectionmetadata_pkey on connectionmetadata cm (cost=0.56..0.76 rows=6 width=31) (actual time=0.004..0.005 rows=1 loops=420,293)

  • Index Cond: ((connectionid = c.id) AND (key = 'AccountingSystemId'::text) AND (system = false))
  • Filter: (NOT system)
  • Heap Fetches: 1,224,499
17. 50.508 50.508 ↓ 0.0 0 122

Index Only Scan using externalconnections_pkey on externalconnections ec (cost=0.43..0.45 rows=1 width=16) (actual time=0.414..0.414 rows=0 loops=122)

  • Index Cond: (id = cm.connectionid)
  • Heap Fetches: 3
Planning time : 1.915 ms
Execution time : 7,192.988 ms