explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VVFc

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 11,114.631 ↓ 27.0 27 1

Limit (cost=36,985.72..215,681.90 rows=1 width=16) (actual time=1,638.202..11,114.631 rows=27 loops=1)

  • Join Filter: ((cm_1.value)::text = (cm.value)::text)
  • Rows Removed by Join Filter: 292,322
  • (actual time=12.310..48.214 rows=100 loops=1)
2.          

CTE csapconnectionids

3. 0.000 48.227 ↑ 1.0 100 1

Limit (cost=1,000.00..36,706.07 rows=100 width=16) (actual time=12.310..48.227 rows=100 loops=1)

4. 5,871.415 5,871.415 ↓ 4.5 9 1

Gather (cost=1000.00..435185.80 rows=1216 width=16) 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)

5. 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)
6. 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
7. 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
8. 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)

9. 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
10. 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
11. 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)

12. 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)

13. 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
14. 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
15. 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
16. 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
17. 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
18. 1,132.127 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
  • Workers Planned: 2
  • Workers Launched: 2
19. 37.197 37.197 ↑ 14.9 34 3 / 3

Parallel Seq Scan on connectionmetadata cm_2 (cost=0.00..434,064.20 rows=507 width=16) (actual time=7.055..37.197 rows=34 loops=3)

  • Filter: (system AND ((key)::text = 'tags'::text) AND ((value)::text = 'CLOUDSCAN_AP_CONNECTION'::text))
  • Rows Removed by Filter: 142,658
20. 1,570.808 11,114.619 ↓ 27.0 27 1

Nested Loop (cost=279.65..178,975.83 rows=1 width=16) (actual time=1,638.201..11,114.619 rows=27 loops=1)

  • Join Filter: ((cm_1.value)::text = (cm.value)::text)
  • Rows Removed by Join Filter: 2,513,907
21. 710.285 1,995.721 ↓ 368.2 2,516,030 1

Hash Join (cost=279.09..173,063.39 rows=6,834 width=31) (actual time=50.499..1,995.721 rows=2,516,030 loops=1)

  • Hash Cond: (c.from_companyaccountid = c_1.from_companyaccountid)
22. 1,234.969 1,234.969 ↑ 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.016..1,234.969 rows=3,761,366 loops=1)

  • Filter: ((kind)::text = 'e'::text)
  • Rows Removed by Filter: 1,992,941
23. 0.027 50.467 ↓ 4.5 91 1

Hash (cost=278.84..278.84 rows=20 width=31) (actual time=50.467..50.467 rows=91 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
24. 0.029 50.440 ↓ 4.5 91 1

Group (cost=278.49..278.64 rows=20 width=31) (actual time=50.407..50.440 rows=91 loops=1)

  • Group Key: c_1.from_companyaccountid, cm_1.value
25. 0.102 50.411 ↓ 4.5 91 1

Sort (cost=278.49..278.54 rows=20 width=31) (actual time=50.405..50.411 rows=91 loops=1)

  • Sort Key: c_1.from_companyaccountid, cm_1.value
  • Sort Method: quicksort Memory: 32kB
26. 0.060 50.309 ↓ 4.5 91 1

Nested Loop (cost=3.25..278.06 rows=20 width=31) (actual time=48.372..50.309 rows=91 loops=1)

27. 0.005 49.248 ↓ 2.9 91 1

Nested Loop (cost=2.68..248.50 rows=31 width=48) (actual time=48.347..49.248 rows=91 loops=1)

28. 0.073 48.343 ↑ 1.0 100 1

HashAggregate (cost=2.25..3.25 rows=100 width=16) (actual time=48.323..48.343 rows=100 loops=1)

  • Group Key: csapconnectionids.id
29. 48.270 48.270 ↑ 1.0 100 1

CTE Scan on csapconnectionids (cost=0.00..2.00 rows=100 width=16) (actual time=12.313..48.270 rows=100 loops=1)

30. 0.900 0.900 ↑ 1.0 1 100

Index Scan using connections_pkey on connections c_1 (cost=0.43..2.45 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=100)

  • Index Cond: (id = csapconnectionids.id)
  • Filter: ((NOT deleted) AND ((kind)::text = 'c'::text))
  • Rows Removed by Filter: 0
31. 1.001 1.001 ↑ 6.0 1 91

Index Only Scan using connectionmetadata_pkey on connectionmetadata cm_1 (cost=0.56..0.89 rows=6 width=31) (actual time=0.011..0.011 rows=1 loops=91)

  • Index Cond: ((connectionid = c_1.id) AND (key = 'AccountingSystemId'::text) AND (system = false))
  • Filter: (NOT system)
  • Heap Fetches: 26
32. 7,548.090 7,548.090 ↑ 6.0 1 2,516,030

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

  • Index Cond: ((connectionid = c.id) AND (key = 'AccountingSystemId'::text) AND (system = false))
  • Filter: (NOT system)
  • Heap Fetches: 637,292
Planning time : 1.848 ms
Execution time : 11,114.773 ms