explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oTuf

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 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)

2.          

CTE csapconnectionids

3. 0.013 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. 11.017 48.214 ↑ 12.2 100 1

Gather (cost=1,000.00..435,185.80 rows=1,216 width=16) (actual time=12.310..48.214 rows=100 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 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
6. 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
7. 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)
8. 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
9. 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
10. 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
11. 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
12. 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)

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

14. 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
15. 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)

16. 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
17. 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
18. 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