explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C8T3

Settings
# exclusive inclusive rows x rows loops node
1. 0.292 636,746.928 ↓ 10.0 10 1

Limit (cost=37,877.59..37,877.60 rows=1 width=1,750) (actual time=636,746.634..636,746.928 rows=10 loops=1)

2. 702.999 636,746.636 ↓ 10.0 10 1

Sort (cost=37,877.59..37,877.60 rows=1 width=1,750) (actual time=636,746.630..636,746.636 rows=10 loops=1)

  • Sort Key: this_.id
  • Sort Method: top-N heapsort Memory: 28kB
3. 1,032.667 636,043.637 ↓ 252,012.0 252,012 1

Nested Loop Left Join (cost=22,485.48..37,877.58 rows=1 width=1,750) (actual time=829.224..636,043.637 rows=252,012 loops=1)

  • Join Filter: (this_.reference_id = reference11_.id)
4. 1,559.579 635,010.970 ↓ 252,012.0 252,012 1

Nested Loop Left Join (cost=22,485.48..37,857.01 rows=1 width=1,598) (actual time=829.213..635,010.970 rows=252,012 loops=1)

  • Join Filter: (natinfo7_.id = fortigatev10_.nat_info_id)
5. 312,669.276 633,451.391 ↓ 252,012.0 252,012 1

Nested Loop Left Join (cost=22,485.48..37,841.16 rows=1 width=1,322) (actual time=829.197..633,451.391 rows=252,012 loops=1)

  • Join Filter: (vipmapping8_.map_to_service_id = service9_.id)
  • Rows Removed by Join Filter: 691016904
6. 0.000 1,230.899 ↓ 252,012.0 252,012 1

Gather (cost=22,485.48..37,722.46 rows=1 width=976) (actual time=826.453..1,230.899 rows=252,012 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 233.867 1,638.393 ↓ 84,004.0 84,004 3 / 3

Hash Join (cost=21,485.48..36,722.36 rows=1 width=976) (actual time=855.775..1,638.393 rows=84,004 loops=3)

  • Hash Cond: ((this_.id = (max(this__1.id))) AND (this_.name = this__1.name))
8. 108.300 550.434 ↑ 1.8 84,004 3 / 3

Hash Left Join (cost=217.82..14,639.77 rows=155,225 width=976) (actual time=0.283..550.434 rows=84,004 loops=3)

  • Hash Cond: (this_.customer_id = domain3_.id)
9. 106.872 442.109 ↑ 1.8 84,004 3 / 3

Hash Left Join (cost=193.64..14,208.13 rows=155,225 width=876) (actual time=0.210..442.109 rows=84,004 loops=3)

  • Hash Cond: (this_.address_book_id = addressboo2_.id)
10. 109.315 335.228 ↑ 1.8 84,004 3 / 3

Hash Left Join (cost=158.22..13,765.24 rows=155,225 width=832) (actual time=0.180..335.228 rows=84,004 loops=3)

  • Hash Cond: (this_.zone_id = policyzone4_.id)
11. 225.802 225.899 ↑ 1.8 84,004 3 / 3

Merge Left Join (cost=71.36..13,096.28 rows=155,225 width=611) (actual time=0.135..225.899 rows=84,004 loops=3)

  • Merge Cond: (this_.id = natinfo7_.netobj_id)
  • -> Parallel Index Scan using nst_netobjs_pkey on nst_netobjs this_ (cost=0.42..12756.59 rows=105005 width=371) (actual time=0.031..1
  • Filter: (version_id = 1)
12. 0.034 0.097 ↓ 0.0 0 3 / 3

Sort (cost=70.94..72.64 rows=680 width=240) (actual time=0.096..0.097 rows=0 loops=3)

  • Sort Key: natinfo7_.netobj_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
13. 0.041 0.063 ↓ 0.0 0 3 / 3

Hash Right Join (cost=20.35..38.95 rows=680 width=240) (actual time=0.062..0.063 rows=0 loops=3)

  • Hash Cond: (vipmapping8_.nat_info_id = natinfo7_.id)
14. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on nst_nat_info_vip_mappings vipmapping8_ (cost=0.00..16.80 rows=680 width=92) (never executed)

15. 0.022 0.022 ↓ 0.0 0 3 / 3

Hash (cost=14.60..14.60 rows=460 width=148) (actual time=0.021..0.022 rows=0 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • -> Seq Scan on nst_nat_info natinfo7_ (cost=0.00..14.60 rows=460 width=148) (actual time=0.020..0.020 rows=0 loops
16. 0.001 0.014 ↓ 0.0 0 3 / 3

Hash (cost=80.61..80.61 rows=500 width=221) (actual time=0.013..0.014 rows=0 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
17. 0.002 0.013 ↓ 0.0 0 3 / 3

Hash Left Join (cost=62.97..80.61 rows=500 width=221) (actual time=0.012..0.013 rows=0 loops=3)

  • Hash Cond: (policyzone4_.admin_domain_id = admindomai6_.id)
18. 0.001 0.011 ↓ 0.0 0 3 / 3

Hash Left Join (cost=35.42..51.74 rows=500 width=145) (actual time=0.011..0.011 rows=0 loops=3)

  • Hash Cond: (policyzone4_.address_book_id = addressboo5_.id)
19. 0.010 0.010 ↓ 0.0 0 3 / 3

Seq Scan on nst_zones policyzone4_ (cost=0.00..15.00 rows=500 width=101) (actual time=0.010..0.010 rows=0 loops=3)

20. 0.000 0.000 ↓ 0.0 0 / 3

Hash (cost=21.30..21.30 rows=1,130 width=44) (never executed)

21. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on nst_address_books addressboo5_ (cost=0.00..21.30 rows=1,130 width=44) (never executed)

22. 0.000 0.000 ↓ 0.0 0 / 3

Hash (cost=17.80..17.80 rows=780 width=76) (never executed)

23. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on nst_admin_domains admindomai6_ (cost=0.00..17.80 rows=780 width=76) (never executed)

24. 0.001 0.009 ↓ 0.0 0 3 / 3

Hash (cost=21.30..21.30 rows=1,130 width=44) (actual time=0.009..0.009 rows=0 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
25. 0.008 0.008 ↓ 0.0 0 3 / 3

Seq Scan on nst_address_books addressboo2_ (cost=0.00..21.30 rows=1,130 width=44) (actual time=0.008..0.008 rows=0 loops=3)

26. 0.010 0.025 ↑ 630.0 1 3 / 3

Hash (cost=16.30..16.30 rows=630 width=100) (actual time=0.024..0.025 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.015 0.015 ↑ 630.0 1 3 / 3

Seq Scan on customers domain3_ (cost=0.00..16.30 rows=630 width=100) (actual time=0.013..0.015 rows=1 loops=3)

28. 202.937 854.092 ↑ 1.0 252,012 3 / 3

Hash (cost=17,487.48..17,487.48 rows=252,012 width=53) (actual time=854.091..854.092 rows=252,012 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 23205kB
29. 445.283 651.155 ↑ 1.0 252,012 3 / 3

HashAggregate (cost=12,447.24..14,967.36 rows=252,012 width=53) (actual time=441.764..651.155 rows=252,012 loops=3)

  • Group Key: this__1.name
30. 205.872 205.872 ↑ 1.0 252,012 3 / 3

Seq Scan on nst_netobjs this__1 (cost=0.00..11,187.18 rows=252,012 width=53) (actual time=0.044..205.872 rows=252,012 loops=3)

  • Filter: (((duplicate_of_id IS NULL) OR (duplicate_of_id = '-1'::integer)) AND (version_id = 1))
31. 319,551.216 319,551.216 ↑ 1.0 2,742 252,012

Seq Scan on nst_services service9_ (cost=0.00..84.42 rows=2,742 width=346) (actual time=0.002..1.268 rows=2,742 loops=252,012)

32. 0.000 0.000 ↓ 0.0 0 252,012

Seq Scan on nst_nat_info_fortigate_vip fortigatev10_ (cost=0.00..12.60 rows=260 width=276) (actual time=0.000..0.000 rows=0 loops=252,012)

33. 0.000 0.000 ↓ 0.0 0 252,012

Seq Scan on nst_references reference11_ (cost=0.00..14.70 rows=470 width=144) (actual time=0.000..0.000 rows=0 loops=252,012)