explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ipwx

Settings
# exclusive inclusive rows x rows loops node
1. 4,829.060 122,106.764 ↓ 2.0 2 1

Limit (cost=701,013.65..723,436.52 rows=1 width=327) (actual time=111,803.257..122,106.764 rows=2 loops=1)

  • Functions: 60
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 29.482 ms, Inlining 97.671 ms, Optimization 2913.218 ms, Emission 1809.700 ms, Total 4850.071 ms
2. 700.531 117,277.704 ↓ 2.0 2 1

Nested Loop (cost=701,013.65..723,436.52 rows=1 width=327) (actual time=106,974.205..117,277.704 rows=2 loops=1)

  • Join Filter: (c.idguid = cr.customerguid)
  • Rows Removed by Join Filter: 666236
3. 26.390 26.390 ↑ 1.0 1 1

Index Scan using idx_customers_identifier on customers c (cost=0.42..8.44 rows=1 width=191) (actual time=26.360..26.390 rows=1 loops=1)

  • Index Cond: ((identifier)::text = '6214717-1'::text)
4. 2,796.999 116,550.783 ↑ 1.0 666,238 1

Merge Join (cost=701,013.23..715,096.03 rows=666,564 width=152) (actual time=106,947.815..116,550.783 rows=666,238 loops=1)

  • Merge Cond: ((cs.customerid)::text = (co.customerkey)::text)
5. 6,625.812 77,623.562 ↓ 1.0 487,730 1

Sort (cost=552,772.64..553,991.28 rows=487,454 width=84) (actual time=76,913.100..77,623.562 rows=487,730 loops=1)

  • Sort Key: cs.customerid
  • Sort Method: external sort Disk: 34672kB
6. 1,602.952 70,997.750 ↓ 1.0 487,747 1

Merge Left Join (cost=437,692.95..483,393.15 rows=487,454 width=84) (actual time=64,461.727..70,997.750 rows=487,747 loops=1)

  • Merge Cond: (cr.customerguid = c_2.idguid)
7. 965.482 32,999.776 ↓ 1.0 487,747 1

Merge Left Join (cost=311,087.50..348,164.64 rows=487,454 width=66) (actual time=28,744.868..32,999.776 rows=487,747 loops=1)

  • Merge Cond: (c_1.idguid = cr_1.customerguid)
8. 1,628.967 26,562.990 ↓ 1.0 487,611 1

Merge Join (cost=156,768.81..192,311.05 rows=487,454 width=40) (actual time=23,303.342..26,562.990 rows=487,611 loops=1)

  • Merge Cond: (cr.customerguid = c_1.idguid)
9. 2,231.827 23,906.703 ↓ 1.0 487,611 1

Sort (cost=156,756.21..157,974.84 rows=487,454 width=24) (actual time=23,281.840..23,906.703 rows=487,611 loops=1)

  • Sort Key: cr.customerguid
  • Sort Method: external merge Disk: 16232kB
10. 2,260.572 21,674.876 ↓ 1.0 487,611 1

Merge Join (cost=212.56..100,704.72 rows=487,454 width=24) (actual time=6.519..21,674.876 rows=487,611 loops=1)

  • Merge Cond: (cr.customershipguid = cs.idguid)
11. 16,486.596 16,486.596 ↓ 1.0 487,611 1

Index Scan using idx_customerrelations_customershipguid on customerrelations cr (cost=0.42..52,261.77 rows=487,454 width=32) (actual time=6.430..16,486.596 rows=487,611 loops=1)

  • Filter: (typecid = 149001)
  • Rows Removed by Filter: 23383
12. 2,927.708 2,927.708 ↑ 1.0 487,668 1

Index Scan using "PK_customerships" on customerships cs (cost=0.42..41,130.60 rows=487,668 width=24) (actual time=0.068..2,927.708 rows=487,668 loops=1)

13. 1,027.320 1,027.320 ↑ 1.0 505,595 1

Index Only Scan using "PK_customers" on customers c_1 (cost=0.42..26,978.09 rows=505,978 width=16) (actual time=21.441..1,027.320 rows=505,595 loops=1)

  • Heap Fetches: 0
14. 93.981 5,471.304 ↑ 1.0 21,270 1

Sort (cost=154,318.15..154,372.27 rows=21,649 width=42) (actual time=5,441.481..5,471.304 rows=21,270 loops=1)

  • Sort Key: cr_1.customerguid
  • Sort Method: quicksort Memory: 2507kB
15. 127.890 5,377.323 ↑ 1.0 21,188 1

Nested Loop Left Join (cost=1.27..152,759.20 rows=21,649 width=42) (actual time=26.836..5,377.323 rows=21,188 loops=1)

16. 118.454 4,740.921 ↑ 1.0 21,188 1

Nested Loop Left Join (cost=0.84..109,417.64 rows=21,649 width=50) (actual time=12.450..4,740.921 rows=21,188 loops=1)

17. 363.679 363.679 ↑ 1.0 21,188 1

Index Scan using idx_customerrelations_typecid on customerrelations cr_1 (cost=0.42..7,184.08 rows=21,649 width=32) (actual time=12.381..363.679 rows=21,188 loops=1)

  • Index Cond: (typecid = 149004)
18. 4,258.788 4,258.788 ↑ 1.0 1 21,188

Index Scan using "PK_customers" on customers c_3 (cost=0.42..4.72 rows=1 width=34) (actual time=0.201..0.201 rows=1 loops=21,188)

  • Index Cond: (idguid = cr_1.customerguid)
19. 508.512 508.512 ↑ 1.0 1 21,188

Index Scan using "PK_customerships" on customerships cs_1 (cost=0.42..2.00 rows=1 width=24) (actual time=0.024..0.024 rows=1 loops=21,188)

  • Index Cond: (idguid = cr_1.customershipguid)
20. 4,875.855 36,395.022 ↑ 1.0 505,595 1

Sort (cost=126,605.44..127,870.39 rows=505,978 width=34) (actual time=35,716.787..36,395.022 rows=505,595 loops=1)

  • Sort Key: c_2.idguid
  • Sort Method: external merge Disk: 22000kB
21. 31,519.167 31,519.167 ↑ 1.0 505,595 1

Seq Scan on customers c_2 (cost=0.00..64,831.78 rows=505,978 width=34) (actual time=0.040..31,519.167 rows=505,595 loops=1)

22. 4,153.272 36,130.222 ↓ 1.0 667,327 1

Materialize (cost=148,240.59..151,576.76 rows=667,235 width=76) (actual time=30,034.620..36,130.222 rows=667,327 loops=1)

23. 10,075.886 31,976.950 ↑ 1.0 667,161 1

Sort (cost=148,240.59..149,908.68 rows=667,235 width=76) (actual time=30,034.602..31,976.950 rows=667,161 loops=1)

  • Sort Key: co.customerkey
  • Sort Method: external merge Disk: 52144kB
24. 21,901.064 21,901.064 ↑ 1.0 667,161 1

Seq Scan on contractcustomers co (cost=0.00..54,044.32 rows=667,235 width=76) (actual time=5.939..21,901.064 rows=667,161 loops=1)

  • Filter: (customerrolecid = 168001)
  • Rows Removed by Filter: 21790
Planning time : 224.727 ms
Execution time : 122,204.254 ms