explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1xcp

Settings
# exclusive inclusive rows x rows loops node
1. 0.132 5,772.667 ↑ 1.0 100 1

Limit (cost=645,557.26..1,007,121.54 rows=100 width=16) (actual time=5,592.337..5,772.667 rows=100 loops=1)

2. 10.318 5,772.535 ↑ 1.7 100 1

Hash Join (cost=645,557.26..1,256,600.90 rows=169 width=16) (actual time=5,592.335..5,772.535 rows=100 loops=1)

  • Hash Cond: ((c.from_companyaccountid = c_1.from_companyaccountid) AND ((cm.value)::text = (cm_1.value)::text))
3. 100.632 2,586.540 ↑ 62.2 39,034 1

Hash Join (cost=206,043.65..804,343.63 rows=2,427,364 width=47) (actual time=2,408.933..2,586.540 rows=39,034 loops=1)

  • Hash Cond: (cm.connectionid = c.id)
4. 105.144 105.144 ↑ 61.2 59,803 1

Seq Scan on connectionmetadata cm (cost=0.00..588,688.40 rows=3,661,550 width=31) (actual time=0.027..105.144 rows=59,803 loops=1)

  • Filter: ((NOT system) AND ((key)::text = 'AccountingSystemId'::text))
  • Rows Removed by Filter: 827,583
5. 1,045.317 2,380.764 ↑ 1.0 3,761,445 1

Hash (cost=158,432.66..158,432.66 rows=3,808,879 width=32) (actual time=2,380.763..2,380.764 rows=3,761,445 loops=1)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 267,859kB
6. 1,335.447 1,335.447 ↑ 1.0 3,761,445 1

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

  • Filter: ((kind)::text = 'e'::text)
  • Rows Removed by Filter: 1,993,315
7. 26.879 3,175.677 ↓ 10.7 107,908 1

Hash (cost=439,362.03..439,362.03 rows=10,105 width=31) (actual time=3,175.677..3,175.677 rows=107,908 loops=1)

  • Buckets: 131,072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 7,625kB
8. 24.800 3,148.798 ↓ 10.7 107,908 1

Group (cost=439,185.19..439,260.98 rows=10,105 width=31) (actual time=3,113.421..3,148.798 rows=107,908 loops=1)

  • Group Key: c_1.from_companyaccountid, cm_1.value
9. 275.175 3,123.998 ↓ 10.8 108,810 1

Sort (cost=439,185.19..439,210.46 rows=10,105 width=31) (actual time=3,113.412..3,123.998 rows=108,810 loops=1)

  • Sort Key: c_1.from_companyaccountid, cm_1.value
  • Sort Method: quicksort Memory: 11,576kB
10. 0.000 2,848.823 ↓ 10.8 108,810 1

Nested Loop (cost=435,189.83..438,513.07 rows=10,105 width=31) (actual time=1,419.095..2,848.823 rows=108,810 loops=1)

11. 41.018 2,092.970 ↓ 287.3 109,453 1

Nested Loop (cost=435,189.27..438,149.78 rows=381 width=48) (actual time=1,419.051..2,092.970 rows=109,453 loops=1)

12. 87.037 1,452.347 ↓ 98.7 119,921 1

HashAggregate (cost=435,188.84..435,200.99 rows=1,215 width=16) (actual time=1,418.999..1,452.347 rows=119,921 loops=1)

  • Group Key: cm_2.connectionid
13. 0.000 1,365.310 ↓ 98.6 119,921 1

Gather (cost=1,000.00..435,185.80 rows=1,216 width=16) (actual time=6.933..1,365.310 rows=119,921 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 1,377.326 1,377.326 ↓ 78.8 39,974 3 / 3

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

  • Filter: (system AND ((key)::text = 'tags'::text) AND ((value)::text = 'CLOUDSCAN_AP_CONNECTION'::text))
  • Rows Removed by Filter: 8,256,644
15. 599.605 599.605 ↑ 1.0 1 119,921

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

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

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

  • Index Cond: ((connectionid = c_1.id) AND (key = 'AccountingSystemId'::text) AND (system = false))
  • Filter: (NOT system)
  • Heap Fetches: 55,947
Planning time : 3.001 ms
Execution time : 5,777.048 ms