explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XbJk

Settings
# exclusive inclusive rows x rows loops node
1. 282.706 12,461.054 ↑ 447.8 1,036,671 1

Merge Left Join (cost=4,490,785.06..13,182,408.08 rows=464,269,866 width=237) (actual time=10,607.278..12,461.054 rows=1,036,671 loops=1)

  • Merge Cond: ((access.source)::text = (source_apps.endpoint)::text)
2.          

CTE applications_view

3. 91.445 700.435 ↓ 1.0 110,001 1

HashAggregate (cost=28,425.69..29,524.93 rows=109,924 width=96) (actual time=670.403..700.435 rows=110,001 loops=1)

  • Group Key: membership.group_id, (array_agg(application.asset_id)), (array_agg(application.application_id))
4. 6.799 608.990 ↓ 1.0 110,001 1

Append (cost=24,563.16..27,601.26 rows=109,924 width=96) (actual time=529.270..608.990 rows=110,001 loops=1)

5. 299.888 569.223 ↓ 1.0 10,001 1

HashAggregate (cost=24,563.16..24,712.02 rows=9,924 width=71) (actual time=529.269..569.223 rows=10,001 loops=1)

  • Group Key: membership.group_id
6. 159.367 269.335 ↑ 1.0 549,664 1

Hash Join (cost=3,040.00..20,440.68 rows=549,664 width=14) (actual time=34.714..269.335 rows=549,664 loops=1)

  • Hash Cond: ((membership.asset_id)::text = (application.asset_id)::text)
7. 75.333 75.333 ↑ 1.0 549,664 1

Seq Scan on membership (cost=0.00..9,842.80 rows=549,664 width=14) (actual time=0.019..75.333 rows=549,664 loops=1)

  • Filter: ((tenant_id)::text = '1'::text)
8. 16.027 34.635 ↑ 1.0 100,000 1

Hash (cost=1,790.00..1,790.00 rows=100,000 width=9) (actual time=34.635..34.635 rows=100,000 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5115kB
9. 18.608 18.608 ↑ 1.0 100,000 1

Seq Scan on application (cost=0.00..1,790.00 rows=100,000 width=9) (actual time=0.011..18.608 rows=100,000 loops=1)

  • Filter: ((tenant_id)::text = '1'::text)
10. 32.968 32.968 ↑ 1.0 100,000 1

Seq Scan on application application_1 (cost=0.00..1,790.00 rows=100,000 width=69) (actual time=0.019..32.968 rows=100,000 loops=1)

  • Filter: ((tenant_id)::text = '1'::text)
11.          

CTE tags_view

12. 165.582 1,828.628 ↓ 1.1 110,001 1

HashAggregate (cost=84,125.59..85,157.81 rows=103,222 width=96) (actual time=1,796.781..1,828.628 rows=110,001 loops=1)

  • Group Key: membership_1.group_id, (array_agg(tag.asset_id)), (array_agg((((tag.tag_key)::text || ':'::text) || (tag.tag_value)::text)))
13. 6.893 1,663.046 ↓ 1.1 110,001 1

Append (cost=73,298.69..83,351.43 rows=103,222 width=96) (actual time=1,337.006..1,663.046 rows=110,001 loops=1)

14. 930.020 1,419.275 ↓ 1.0 10,001 1

HashAggregate (cost=73,298.69..73,447.55 rows=9,924 width=71) (actual time=1,337.006..1,419.275 rows=10,001 loops=1)

  • Group Key: membership_1.group_id
15. 321.986 489.255 ↑ 1.1 1,486,147 1

Hash Join (cost=7,705.44..53,395.84 rows=1,592,228 width=20) (actual time=89.913..489.255 rows=1,486,147 loops=1)

  • Hash Cond: ((membership_1.asset_id)::text = (tag.asset_id)::text)
16. 79.035 79.035 ↑ 1.0 549,664 1

Seq Scan on membership membership_1 (cost=0.00..9,842.80 rows=549,664 width=12) (actual time=0.011..79.035 rows=549,664 loops=1)

  • Filter: ((tenant_id)::text = '1'::text)
17. 51.521 88.234 ↑ 1.0 270,286 1

Hash (cost=4,326.86..4,326.86 rows=270,286 width=13) (actual time=88.234..88.234 rows=270,286 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 16433kB
18. 36.713 36.713 ↑ 1.0 270,286 1

Seq Scan on tag (cost=0.00..4,326.86 rows=270,286 width=13) (actual time=0.011..36.713 rows=270,286 loops=1)

19. 197.063 236.878 ↓ 1.1 100,000 1

HashAggregate (cost=7,705.44..8,871.66 rows=93,298 width=69) (actual time=190.688..236.878 rows=100,000 loops=1)

  • Group Key: tag_1.asset_id
20. 39.815 39.815 ↑ 1.0 270,286 1

Seq Scan on tag tag_1 (cost=0.00..5,002.58 rows=270,286 width=13) (actual time=0.017..39.815 rows=270,286 loops=1)

  • Filter: ((tenant_id)::text = '1'::text)
21. 336.864 11,826.731 ↑ 89.8 1,036,671 1

Merge Left Join (cost=4,364,699.82..5,859,662.95 rows=93,044,874 width=173) (actual time=10,361.451..11,826.731 rows=1,036,671 loops=1)

  • Merge Cond: ((access.source)::text = (source_tags.endpoint)::text)
22. 5,036.149 11,121.426 ↑ 19.2 1,036,671 1

Sort (cost=4,354,039.37..4,403,684.28 rows=19,857,965 width=141) (actual time=10,107.580..11,121.426 rows=1,036,671 loops=1)

  • Sort Key: access.source
  • Sort Method: external merge Disk: 1642336kB
23. 293.937 6,085.277 ↑ 19.2 1,036,671 1

Merge Join (cost=146,253.02..521,580.85 rows=19,857,965 width=141) (actual time=5,215.247..6,085.277 rows=1,036,671 loops=1)

  • Merge Cond: ((access.destination)::text = (destination_apps.endpoint)::text)
24. 221.241 4,690.602 ↑ 4.2 1,036,669 1

Merge Join (cost=134,850.53..201,317.17 rows=4,396,702 width=109) (actual time=4,228.947..4,690.602 rows=1,036,669 loops=1)

  • Merge Cond: ((destination_tags.endpoint)::text = (access.destination)::text)
25. 276.369 2,169.450 ↓ 1.1 109,999 1

Sort (cost=10,660.45..10,918.51 rows=103,222 width=64) (actual time=2,142.075..2,169.450 rows=109,999 loops=1)

  • Sort Key: destination_tags.endpoint
  • Sort Method: quicksort Memory: 45414kB
26. 1,893.081 1,893.081 ↓ 1.1 110,001 1

CTE Scan on tags_view destination_tags (cost=0.00..2,064.44 rows=103,222 width=64) (actual time=1,796.789..1,893.081 rows=110,001 loops=1)

27. 2,162.523 2,299.911 ↓ 1.0 1,036,669 1

Sort (cost=124,190.07..126,781.74 rows=1,036,668 width=45) (actual time=2,086.865..2,299.911 rows=1,036,669 loops=1)

  • Sort Key: access.destination
  • Sort Method: quicksort Memory: 134775kB
28. 137.388 137.388 ↑ 1.0 1,036,668 1

Seq Scan on access (cost=0.00..20,608.68 rows=1,036,668 width=45) (actual time=0.012..137.388 rows=1,036,668 loops=1)

29. 355.224 1,100.738 ↓ 9.8 1,073,341 1

Sort (cost=11,402.50..11,677.31 rows=109,924 width=96) (actual time=986.296..1,100.738 rows=1,073,341 loops=1)

  • Sort Key: destination_apps.endpoint
  • Sort Method: quicksort Memory: 29062kB
30. 745.514 745.514 ↓ 1.0 110,001 1

CTE Scan on applications_view destination_apps (cost=0.00..2,198.48 rows=109,924 width=96) (actual time=670.406..745.514 rows=110,001 loops=1)

31. 349.597 368.441 ↓ 10.4 1,073,341 1

Sort (cost=10,660.45..10,918.51 rows=103,222 width=64) (actual time=253.861..368.441 rows=1,073,341 loops=1)

  • Sort Key: source_tags.endpoint
  • Sort Method: quicksort Memory: 45414kB
32. 18.844 18.844 ↓ 1.1 110,001 1

CTE Scan on tags_view source_tags (cost=0.00..2,064.44 rows=103,222 width=64) (actual time=0.002..18.844 rows=110,001 loops=1)

33. 335.992 351.617 ↓ 9.8 1,073,341 1

Sort (cost=11,402.50..11,677.31 rows=109,924 width=96) (actual time=245.819..351.617 rows=1,073,341 loops=1)

  • Sort Key: source_apps.endpoint
  • Sort Method: quicksort Memory: 29062kB
34. 15.625 15.625 ↓ 1.0 110,001 1

CTE Scan on applications_view source_apps (cost=0.00..2,198.48 rows=109,924 width=96) (actual time=0.001..15.625 rows=110,001 loops=1)