explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F7Ic

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 5,260.380 ↑ 1.0 1 1

Limit (cost=30,470.47..30,473.37 rows=1 width=9) (actual time=5,260.368..5,260.380 rows=1 loops=1)

2. 0.002 5,260.367 ↑ 11,472.0 1 1

Nested Loop Left Join (cost=30,470.47..63,697.31 rows=11,472 width=9) (actual time=5,260.367..5,260.367 rows=1 loops=1)

3. 0.001 0.040 ↑ 11,472.0 1 1

Nested Loop Left Join (cost=105.19..31,720.20 rows=11,472 width=13) (actual time=0.040..0.040 rows=1 loops=1)

4. 0.004 0.023 ↑ 11,472.0 1 1

Merge Left Join (cost=104.77..26,306.52 rows=11,472 width=17) (actual time=0.023..0.023 rows=1 loops=1)

  • Merge Cond: (pay.creditfk = c.pkey)
5. 0.009 0.009 ↑ 11,472.0 1 1

Index Scan using ix_payment_creditfk on payment pay (cost=0.29..1,565.45 rows=11,472 width=12) (actual time=0.008..0.009 rows=1 loops=1)

6. 0.010 0.010 ↑ 9,693.8 10 1

Index Scan using pk__credit__5e190d187b016d94 on credit c (cost=0.42..24,362.90 rows=96,938 width=21) (actual time=0.006..0.010 rows=10 loops=1)

7. 0.016 0.016 ↑ 1.0 1 1

Index Only Scan using ix_constructiondepot_creditfk on constructiondepot con (cost=0.42..0.46 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (creditfk = c.pkey)
  • Heap Fetches: 0
8. 0.014 5,260.325 ↑ 1.0 1 1

Hash Right Join (cost=30,365.29..30,365.42 rows=1 width=4) (actual time=5,260.325..5,260.325 rows=1 loops=1)

  • Hash Cond: (grouped.dossierentityid = cd.pkey)
9. 5,260.295 5,260.295 ↓ 2.0 8 1

CTE Scan on grouped (cost=30,364.95..30,365.03 rows=4 width=1,408) (actual time=5,260.229..5,260.295 rows=8 loops=1)

10.          

CTE contractors

11. 274.731 3,122.788 ↓ 199.9 156,937 1

WindowAgg (cost=22,650.08..22,779.60 rows=785 width=738) (actual time=2,483.880..3,122.788 rows=156,937 loops=1)

12. 233.391 2,534.183 ↓ 199.9 156,937 1

Sort (cost=22,650.08..22,652.04 rows=785 width=248) (actual time=2,483.854..2,534.183 rows=156,937 loops=1)

  • Sort Key: cd_1.pkey, r.roletypecd, r.pkey
  • Sort Method: external merge Disk: 28312kB
13. 72.728 2,300.792 ↓ 199.9 156,937 1

Nested Loop Left Join (cost=9,676.21..22,612.33 rows=785 width=248) (actual time=543.543..2,300.792 rows=156,937 loops=1)

14. 231.871 1,914.190 ↓ 199.9 156,937 1

Nested Loop Left Join (cost=9,675.92..22,311.97 rows=785 width=232) (actual time=543.530..1,914.190 rows=156,937 loops=1)

15. 49.307 1,368.445 ↓ 199.9 156,937 1

Hash Left Join (cost=9,675.50..17,038.50 rows=785 width=212) (actual time=543.515..1,368.445 rows=156,937 loops=1)

  • Hash Cond: (p.partytypecd = ppt.codeid)
16. 52.268 1,319.131 ↓ 199.9 156,937 1

Hash Left Join (cost=9,674.38..17,031.58 rows=785 width=202) (actual time=543.500..1,319.131 rows=156,937 loops=1)

  • Hash Cond: (pd.gendercd = par_gendercaption.codeid)
17. 72.295 1,266.851 ↓ 199.9 156,937 1

Nested Loop Left Join (cost=9,673.19..17,025.18 rows=785 width=198) (actual time=543.472..1,266.851 rows=156,937 loops=1)

18. 321.871 880.682 ↓ 199.9 156,937 1

Hash Right Join (cost=9,672.77..16,625.41 rows=785 width=198) (actual time=543.456..880.682 rows=156,937 loops=1)

  • Hash Cond: ((pd.isactivepartydetail)::text = (p.partydetaillinkid)::text)
19. 16.994 16.994 ↑ 1.0 170,426 1

Seq Scan on partydetail pd (cost=0.00..6,304.21 rows=170,821 width=91) (actual time=0.094..16.994 rows=170,426 loops=1)

20. 93.833 541.817 ↓ 199.9 156,937 1

Hash (cost=9,662.96..9,662.96 rows=785 width=181) (actual time=541.817..541.817 rows=156,937 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
21. 92.380 447.984 ↓ 199.9 156,937 1

Nested Loop (cost=0.42..9,662.96 rows=785 width=181) (actual time=0.122..447.984 rows=156,937 loops=1)

22. 41.730 41.730 ↓ 199.9 156,937 1

Seq Scan on role r (cost=0.00..4,711.52 rows=785 width=32) (actual time=0.113..41.730 rows=156,937 loops=1)

  • Filter: ((roletypecd = ANY ('{1,2}'::integer[])) AND ((activerole)::integer = 1))
  • Rows Removed by Filter: 68
23. 313.874 313.874 ↑ 1.0 1 156,937

Index Scan using pk__party__5e190d180bba75a2 on party p (cost=0.42..6.31 rows=1 width=157) (actual time=0.002..0.002 rows=1 loops=156,937)

  • Index Cond: (pkey = r.partyfk)
24. 313.874 313.874 ↑ 1.0 1 156,937

Index Scan using pk_partydetailregion on partydetailregion pdr (cost=0.42..0.51 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=156,937)

  • Index Cond: (pd.partydetailregionfk = pkey)
25. 0.007 0.012 ↑ 1.0 3 1

Hash (cost=1.15..1.15 rows=3 width=12) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on par_gendercaption (cost=0.00..1.15 rows=3 width=12) (actual time=0.004..0.005 rows=3 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 9
27. 0.003 0.007 ↑ 1.0 2 1

Hash (cost=1.10..1.10 rows=2 width=14) (actual time=0.007..0.007 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.004 0.004 ↑ 1.0 2 1

Seq Scan on par_partytypecaption ppt (cost=0.00..1.10 rows=2 width=14) (actual time=0.003..0.004 rows=2 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 6
29. 313.874 313.874 ↑ 1.0 1 156,937

Index Scan using pk__credit__5e190d187b016d94 on credit c_1 (cost=0.42..6.72 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=156,937)

  • Index Cond: (pkey = r.creditfk)
30. 313.874 313.874 ↑ 1.0 1 156,937

Index Scan using pk_creditdossier on creditdossier cd_1 (cost=0.29..0.38 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=156,937)

  • Index Cond: (pkey = c_1.creditdossierfk)
31.          

SubPlan (forWindowAgg)

32. 156.937 313.874 ↑ 1.0 1 156,937

Aggregate (cost=0.12..0.13 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=156,937)

33. 156.937 156.937 ↑ 1.0 8 156,937

Values Scan on "*VALUES*" (cost=0.00..0.10 rows=8 width=8) (actual time=0.000..0.001 rows=8 loops=156,937)

34.          

CTE grouped

35. 0.071 5,260.274 ↓ 2.0 8 1

GroupAggregate (cost=7,584.36..7,585.35 rows=4 width=1,140) (actual time=5,260.223..5,260.274 rows=8 loops=1)

  • Group Key: con_1.dossierentityid
36. 130.112 5,260.203 ↓ 3.5 14 1

Sort (cost=7,584.36..7,584.37 rows=4 width=4,040) (actual time=5,260.199..5,260.203 rows=14 loops=1)

  • Sort Key: con_1.dossierentityid
  • Sort Method: external merge Disk: 24896kB
37. 193.893 5,130.091 ↓ 37,204.2 148,817 1

Hash Right Join (cost=7,254.42..7,584.32 rows=4 width=4,040) (actual time=3,604.075..5,130.091 rows=148,817 loops=1)

  • Hash Cond: (conadd.partydetailfk = con_1.pdpkey)
38. 32.159 1,552.119 ↓ 31,958.0 159,790 1

Subquery Scan on conadd (cost=7,236.70..7,566.58 rows=5 width=84) (actual time=214.234..1,552.119 rows=159,790 loops=1)

  • Filter: (conadd.rn = 1)
  • Rows Removed by Filter: 28937
39. 1,259.515 1,519.960 ↓ 185.9 188,727 1

WindowAgg (cost=7,236.70..7,553.89 rows=1,015 width=96) (actual time=214.233..1,519.960 rows=188,727 loops=1)

40. 194.635 260.445 ↓ 185.9 188,727 1

Sort (cost=7,236.70..7,239.24 rows=1,015 width=62) (actual time=214.175..260.445 rows=188,727 loops=1)

  • Sort Key: address.partydetailfk, address.addresstypecd, address.pkey
  • Sort Method: external merge Disk: 15336kB
41. 65.810 65.810 ↓ 185.9 188,727 1

Seq Scan on address (cost=0.00..7,186.02 rows=1,015 width=62) (actual time=0.117..65.810 rows=188,727 loops=1)

  • Filter: ((addresstypecd = ANY ('{1,11}'::integer[])) AND ((isactive)::integer = 1))
  • Rows Removed by Filter: 14889
42. 82.682 3,384.079 ↓ 37,082.0 148,328 1

Hash (cost=17.66..17.66 rows=4 width=3,964) (actual time=3,384.079..3,384.079 rows=148,328 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
43. 3,301.397 3,301.397 ↓ 37,082.0 148,328 1

CTE Scan on contractors con_1 (cost=0.00..17.66 rows=4 width=3,964) (actual time=2,483.884..3,301.397 rows=148,328 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 8609
44. 0.004 0.016 ↑ 1.0 1 1

Hash (cost=0.32..0.32 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.012 0.012 ↑ 1.0 1 1

Index Only Scan using pk_creditdossier on creditdossier cd (cost=0.29..0.32 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=1)

  • Index Cond: (pkey = c.creditdossierfk)
  • Heap Fetches: 0
Planning time : 3.816 ms
Execution time : 5,277.031 ms