explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hvsJ

Settings
# exclusive inclusive rows x rows loops node
1. 377.254 31,460.100 ↓ 2.0 2 1

GroupAggregate (cost=5,988.95..5,988.97 rows=1 width=45) (actual time=30,987.167..31,460.100 rows=2 loops=1)

  • Group Key: mp.id, root.id
2. 488.296 31,082.846 ↓ 971,574.0 971,574 1

Sort (cost=5,988.95..5,988.96 rows=1 width=45) (actual time=30,972.699..31,082.846 rows=971,574 loops=1)

  • Sort Key: mp.id, root.id
  • Sort Method: external merge Disk: 51,288kB
3. 292.636 30,594.550 ↓ 971,574.0 971,574 1

Nested Loop Anti Join (cost=3,923.27..5,988.94 rows=1 width=45) (actual time=16,325.448..30,594.550 rows=971,574 loops=1)

4. 289.745 30,301.914 ↓ 3,361.8 971,574 1

Nested Loop Left Join (cost=3,922.99..5,896.48 rows=289 width=53) (actual time=16,325.445..30,301.914 rows=971,574 loops=1)

5. 602.247 30,012.169 ↓ 3,361.8 971,574 1

Nested Loop (cost=3,922.70..5,803.75 rows=289 width=53) (actual time=16,325.443..30,012.169 rows=971,574 loops=1)

6. 221.496 23,580.424 ↓ 700.5 971,583 1

Nested Loop (cost=3,922.27..5,136.89 rows=1,387 width=61) (actual time=16,324.855..23,580.424 rows=971,583 loops=1)

  • Join Filter: (covg.id = sub.vendorgroup_id)
7. 237.492 16,557.861 ↓ 1,410.1 971,581 1

Hash Right Join (cost=3,921.71..4,637.75 rows=689 width=61) (actual time=16,324.465..16,557.861 rows=971,581 loops=1)

  • Hash Cond: (vs.vendorconnection_id = vc.id)
  • Filter: ((vs.id IS NULL) OR vs.trashed OR ((date(vs.startdate) <= date(now())) AND ((vs.enddate IS NULL) OR (date(vs.enddate) > date(now())))))
8. 8.582 8.582 ↓ 1.0 22,761 1

Seq Scan on vendorship vs (cost=0.00..553.56 rows=22,656 width=33) (actual time=0.448..8.582 rows=22,761 loops=1)

9. 171.461 16,311.787 ↓ 362.4 971,581 1

Hash (cost=3,888.20..3,888.20 rows=2,681 width=61) (actual time=16,311.787..16,311.787 rows=971,581 loops=1)

  • Buckets: 131,072 (originally 4096) Batches: 2 (originally 1) Memory Usage: 84,520kB
10. 278.566 16,140.326 ↓ 362.4 971,581 1

Hash Right Join (cost=3,188.94..3,888.20 rows=2,681 width=61) (actual time=15,870.555..16,140.326 rows=971,581 loops=1)

  • Hash Cond: (vc.vendorgroup_id = covg.id)
11. 5.902 5.902 ↓ 1.0 23,571 1

Seq Scan on vendorconnection vc (cost=0.00..611.00 rows=23,500 width=16) (actual time=0.006..5.902 rows=23,571 loops=1)

12. 303.095 15,855.858 ↓ 362.4 971,581 1

Hash (cost=3,155.43..3,155.43 rows=2,681 width=53) (actual time=15,855.858..15,855.858 rows=971,581 loops=1)

  • Buckets: 131,072 (originally 4096) Batches: 16 (originally 1) Memory Usage: 7,073kB
13. 757.299 15,552.763 ↓ 362.4 971,581 1

Nested Loop (cost=64.16..3,155.43 rows=2,681 width=53) (actual time=1.836..15,552.763 rows=971,581 loops=1)

14. 148.402 9,152.792 ↓ 288.1 1,410,668 1

Nested Loop (cost=63.73..727.51 rows=4,897 width=45) (actual time=1.826..9,152.792 rows=1,410,668 loops=1)

15. 0.025 1.760 ↓ 14.0 14 1

Nested Loop (cost=63.17..130.58 rows=1 width=45) (actual time=1.022..1.760 rows=14 loops=1)

16. 0.370 1.329 ↓ 2.8 14 1

Hash Join (cost=63.03..129.73 rows=5 width=53) (actual time=0.649..1.329 rows=14 loops=1)

  • Hash Cond: (COALESCE(s.parentclient_id, s.id) = root.id)
  • Join Filter: ((COALESCE(s.billinggroupuuid, root.billinggroupuuid))::text = '1bb1b673-0684-4289-857e-c7b81d3ffe16'::text)
  • Rows Removed by Join Filter: 969
17. 0.361 0.361 ↓ 1.0 983 1

Seq Scan on client s (cost=0.00..50.79 rows=979 width=60) (actual time=0.006..0.361 rows=983 loops=1)

18. 0.205 0.598 ↓ 1.0 983 1

Hash (cost=50.79..50.79 rows=979 width=73) (actual time=0.598..0.598 rows=983 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 84kB
19. 0.393 0.393 ↓ 1.0 983 1

Seq Scan on client root (cost=0.00..50.79 rows=979 width=73) (actual time=0.001..0.393 rows=983 loops=1)

20. 0.406 0.406 ↑ 1.0 1 14

Index Only Scan using monitorpackage_pkey on monitorpackage mp (cost=0.14..0.16 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=14)

  • Index Cond: (id = COALESCE(s.monitorpackage_id, root.monitorpackage_id))
  • Heap Fetches: 0
21. 9,002.630 9,002.630 ↓ 13.1 100,762 14

Index Scan using clientownedvendor_manager_idx on clientownedvendor cov (cost=0.56..519.74 rows=7,720 width=16) (actual time=0.489..643.045 rows=100,762 loops=14)

  • Index Cond: (manager_id = s.id)
22. 5,642.672 5,642.672 ↑ 1.0 1 1,410,668

Index Scan using clientownedvendorgroup_pkey on clientownedvendorgroup covg (cost=0.43..0.49 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,410,668)

  • Index Cond: (id = cov.vendorgroup_id)
  • Filter: (NOT trashed)
  • Rows Removed by Filter: 0
23. 6,801.067 6,801.067 ↑ 1.0 1 971,581

Index Scan using monitoredsubject2_vendorgroup_idx on monitoredsubject2 sub (cost=0.56..0.71 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=971,581)

  • Index Cond: (vendorgroup_id = cov.vendorgroup_id)
24. 5,829.498 5,829.498 ↑ 1.0 1 971,583

Index Scan using monitorservice2_subject on monitorservice2 ms (cost=0.43..0.47 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=971,583)

  • Index Cond: (monitorsubject_id = sub.id)
  • Filter: ((currentstatus)::text = 'active'::text)
  • Rows Removed by Filter: 0
25. 0.000 0.000 ↓ 0.0 0 971,574

Index Scan using uk_psi3tv38rd5k9vowx7g427emd on vendorpayment_vendorship vpvs (cost=0.29..0.31 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=971,574)

  • Index Cond: (vendorships_id = vs.id)
26. 0.000 0.000 ↓ 0.0 0 971,574

Index Only Scan using vendorpayment_pkey on vendorpayment vp (cost=0.29..0.31 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=971,574)

  • Index Cond: (id = vpvs.vendorpayment_id)
  • Heap Fetches: 0
Planning time : 109.751 ms
Execution time : 31,467.872 ms