explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lVn7

Settings
# exclusive inclusive rows x rows loops node
1. 1,038.060 8,635.819 ↓ 92,383.0 92,383 1

Hash Full Join (cost=46,129.91..46,130.29 rows=1 width=1,408) (actual time=6,752.504..8,635.819 rows=92,383 loops=1)

  • Hash Cond: (a.dossierentityid = b.dossierentityid)
2.          

CTE contractors

3. 247.075 3,005.873 ↓ 199.9 156,937 1

WindowAgg (cost=22,651.58..22,781.10 rows=785 width=738) (actual time=2,395.704..3,005.873 rows=156,937 loops=1)

4. 228.240 2,444.924 ↓ 199.9 156,937 1

Sort (cost=22,651.58..22,653.54 rows=785 width=248) (actual time=2,395.679..2,444.924 rows=156,937 loops=1)

  • Sort Key: cd.pkey, r.roletypecd, r.pkey
  • Sort Method: external merge Disk: 28296kB
5. 45.678 2,216.684 ↓ 199.9 156,937 1

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

6. 200.067 1,857.132 ↓ 199.9 156,937 1

Nested Loop Left Join (cost=9,675.92..22,313.47 rows=785 width=232) (actual time=542.191..1,857.132 rows=156,937 loops=1)

7. 50.725 1,343.191 ↓ 199.9 156,937 1

Hash Left Join (cost=9,675.50..17,040.00 rows=785 width=212) (actual time=542.177..1,343.191 rows=156,937 loops=1)

  • Hash Cond: (p.partytypecd = ppt.codeid)
8. 52.206 1,292.460 ↓ 199.9 156,937 1

Hash Left Join (cost=9,674.38..17,033.07 rows=785 width=202) (actual time=542.163..1,292.460 rows=156,937 loops=1)

  • Hash Cond: (pd.gendercd = par_gendercaption.codeid)
9. 52.608 1,240.245 ↓ 199.9 156,937 1

Nested Loop Left Join (cost=9,673.19..17,026.68 rows=785 width=198) (actual time=542.139..1,240.245 rows=156,937 loops=1)

10. 316.282 873.763 ↓ 199.9 156,937 1

Hash Right Join (cost=9,672.77..16,626.92 rows=785 width=198) (actual time=542.124..873.763 rows=156,937 loops=1)

  • Hash Cond: ((pd.isactivepartydetail)::text = (p.partydetaillinkid)::text)
11. 16.983 16.983 ↑ 1.0 170,427 1

Seq Scan on partydetail pd (cost=0.00..6,305.58 rows=170,858 width=91) (actual time=0.086..16.983 rows=170,427 loops=1)

12. 92.835 540.498 ↓ 199.9 156,937 1

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

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
13. 91.540 447.663 ↓ 199.9 156,937 1

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

14. 42.249 42.249 ↓ 199.9 156,937 1

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

  • Filter: ((roletypecd = ANY ('{1,2}'::integer[])) AND ((activerole)::integer = 1))
  • Rows Removed by Filter: 68
15. 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)
16. 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)
17. 0.005 0.009 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.004 0.004 ↑ 1.0 3 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.003 0.003 ↑ 1.0 2 1

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

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

Index Scan using pk__credit__5e190d187b016d94 on credit c (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)
22. 313.874 313.874 ↑ 1.0 1 156,937

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

  • Index Cond: (pkey = c.creditdossierfk)
23.          

SubPlan (forWindowAgg)

24. 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)

25. 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)

26.          

CTE roletypecd1

27. 319.710 3,465.748 ↓ 92,383.0 92,383 1

GroupAggregate (cost=16,054.11..16,054.28 rows=1 width=1,140) (actual time=2,928.855..3,465.748 rows=92,383 loops=1)

  • Group Key: c_1.dossierentityid
28. 149.802 2,961.272 ↓ 92,383.0 92,383 1

Sort (cost=16,054.11..16,054.11 rows=1 width=4,112) (actual time=2,928.826..2,961.272 rows=92,383 loops=1)

  • Sort Key: c_1.dossierentityid
  • Sort Method: external merge Disk: 19744kB
29. 111.795 2,811.470 ↓ 92,383.0 92,383 1

Merge Right Join (cost=15,493.73..16,054.10 rows=1 width=4,112) (actual time=617.131..2,811.470 rows=92,383 loops=1)

  • Merge Cond: (conadd11.partydetailfk = c_1.pdpkey)
30. 24.759 1,083.223 ↓ 39,821.0 159,284 1

Subquery Scan on conadd11 (cost=7,737.05..8,017.20 rows=4 width=80) (actual time=163.979..1,083.223 rows=159,284 loops=1)

  • Filter: (conadd11.rn = 1)
31. 860.188 1,058.464 ↓ 184.8 159,284 1

WindowAgg (cost=7,737.05..8,006.42 rows=862 width=96) (actual time=163.978..1,058.464 rows=159,284 loops=1)

32. 137.556 198.276 ↓ 184.8 159,284 1

Sort (cost=7,737.05..7,739.20 rows=862 width=62) (actual time=163.921..198.276 rows=159,284 loops=1)

  • Sort Key: address.partydetailfk, address.pkey
  • Sort Method: external merge Disk: 12552kB
33. 60.720 60.720 ↓ 184.8 159,292 1

Seq Scan on address (cost=0.00..7,695.02 rows=862 width=62) (actual time=0.106..60.720 rows=159,292 loops=1)

  • Filter: ((addresstypecd = ANY ('{1,11}'::integer[])) AND (addresstypecd = 11) AND ((isactive)::integer = 1))
  • Rows Removed by Filter: 44325
34. 44.629 1,616.452 ↓ 92,383.0 92,383 1

Materialize (cost=7,756.68..8,036.87 rows=1 width=4,040) (actual time=448.062..1,616.452 rows=92,383 loops=1)

35. 91.586 1,571.823 ↓ 92,383.0 92,383 1

Merge Left Join (cost=7,756.68..8,036.87 rows=1 width=4,040) (actual time=448.057..1,571.823 rows=92,383 loops=1)

  • Merge Cond: (c_1.pdpkey = conadd1.partydetailfk)
36. 150.569 201.383 ↓ 92,383.0 92,383 1

Sort (cost=19.64..19.64 rows=1 width=3,964) (actual time=161.008..201.383 rows=92,383 loops=1)

  • Sort Key: c_1.pdpkey
  • Sort Method: external merge Disk: 11368kB
37. 50.814 50.814 ↓ 92,383.0 92,383 1

CTE Scan on contractors c_1 (cost=0.00..19.62 rows=1 width=3,964) (actual time=0.015..50.814 rows=92,383 loops=1)

  • Filter: ((rn = 1) AND (roletypecd = 1))
  • Rows Removed by Filter: 64554
38. 53.456 1,278.854 ↓ 40,185.8 160,743 1

Materialize (cost=7,737.05..8,017.21 rows=4 width=80) (actual time=281.518..1,278.854 rows=160,743 loops=1)

39. 29.194 1,225.398 ↓ 39,821.0 159,284 1

Subquery Scan on conadd1 (cost=7,737.05..8,017.20 rows=4 width=80) (actual time=281.510..1,225.398 rows=159,284 loops=1)

  • Filter: (conadd1.rn = 1)
40. 878.825 1,196.204 ↓ 184.8 159,284 1

WindowAgg (cost=7,737.05..8,006.42 rows=862 width=96) (actual time=281.508..1,196.204 rows=159,284 loops=1)

41. 200.920 317.379 ↓ 184.8 159,284 1

Sort (cost=7,737.05..7,739.20 rows=862 width=62) (actual time=281.456..317.379 rows=159,284 loops=1)

  • Sort Key: address_1.partydetailfk, address_1.pkey
  • Sort Method: external merge Disk: 12552kB
42. 116.459 116.459 ↓ 184.8 159,292 1

Seq Scan on address address_1 (cost=0.00..7,695.02 rows=862 width=62) (actual time=0.144..116.459 rows=159,292 loops=1)

  • Filter: ((addresstypecd = ANY ('{1,11}'::integer[])) AND (addresstypecd = 11) AND ((isactive)::integer = 1))
  • Rows Removed by Filter: 44325
43.          

SubPlan (forGroupAggregate)

44. 92.383 92.383 ↑ 1.0 1 92,383

Aggregate (cost=0.03..0.04 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=92,383)

45. 0.000 0.000 ↑ 1.0 2 92,383

Values Scan on "*VALUES*_2" (cost=0.00..0.03 rows=2 width=8) (actual time=0.000..0.000 rows=2 loops=92,383)

46. 92.383 92.383 ↑ 1.0 1 92,383

Aggregate (cost=0.03..0.04 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=92,383)

47. 0.000 0.000 ↑ 1.0 2 92,383

Values Scan on "*VALUES*_1" (cost=0.00..0.03 rows=2 width=8) (actual time=0.000..0.000 rows=2 loops=92,383)

48.          

CTE roletypecd2

49. 86.349 3,741.432 ↓ 55,945.0 55,945 1

GroupAggregate (cost=7,294.44..7,294.50 rows=1 width=820) (actual time=3,645.694..3,741.432 rows=55,945 loops=1)

  • Group Key: c_2.dossierentityid
50. 54.141 3,655.083 ↓ 56,006.0 56,006 1

Sort (cost=7,294.44..7,294.45 rows=1 width=3,968) (actual time=3,645.681..3,655.083 rows=56,006 loops=1)

  • Sort Key: c_2.dossierentityid
  • Sort Method: external merge Disk: 6496kB
51. 35.103 3,600.942 ↓ 56,006.0 56,006 1

Merge Right Join (cost=7,256.34..7,294.43 rows=1 width=3,968) (actual time=3,437.626..3,600.942 rows=56,006 loops=1)

  • Merge Cond: (conadd.partydetailfk = c_2.pdpkey)
52. 14.267 305.073 ↓ 31,956.6 159,783 1

Subquery Scan on conadd (cost=7,236.70..7,274.77 rows=5 width=20) (actual time=189.511..305.073 rows=159,783 loops=1)

  • Filter: (conadd.rn = 1)
  • Rows Removed by Filter: 81
53. 84.152 290.806 ↓ 157.5 159,864 1

WindowAgg (cost=7,236.70..7,262.08 rows=1,015 width=1,176) (actual time=189.510..290.806 rows=159,864 loops=1)

54. 151.992 206.654 ↓ 157.5 159,864 1

Sort (cost=7,236.70..7,239.24 rows=1,015 width=28) (actual time=189.503..206.654 rows=159,864 loops=1)

  • Sort Key: address_2.partydetailfk, address_2.addresstypecd, address_2.pkey
  • Sort Method: external merge Disk: 7344kB
55. 54.662 54.662 ↓ 185.9 188,728 1

Seq Scan on address address_2 (cost=0.00..7,186.02 rows=1,015 width=28) (actual time=0.101..54.662 rows=188,728 loops=1)

  • Filter: ((addresstypecd = ANY ('{1,11}'::integer[])) AND ((isactive)::integer = 1))
  • Rows Removed by Filter: 14889
56. 88.851 3,260.766 ↓ 56,006.0 56,006 1

Sort (cost=19.64..19.64 rows=1 width=3,956) (actual time=3,247.011..3,260.766 rows=56,006 loops=1)

  • Sort Key: c_2.pdpkey
  • Sort Method: external sort Disk: 6184kB
57. 3,171.915 3,171.915 ↓ 55,945.0 55,945 1

CTE Scan on contractors c_2 (cost=0.00..19.62 rows=1 width=3,956) (actual time=2,395.735..3,171.915 rows=55,945 loops=1)

  • Filter: ((rn = 1) AND (roletypecd = 2))
  • Rows Removed by Filter: 100992
58. 3,589.466 3,589.466 ↓ 92,383.0 92,383 1

CTE Scan on roletypecd1 a (cost=0.00..0.02 rows=1 width=744) (actual time=2,928.858..3,589.466 rows=92,383 loops=1)

59. 24.630 3,823.527 ↓ 55,945.0 55,945 1

Hash (cost=0.02..0.02 rows=1 width=424) (actual time=3,823.527..3,823.527 rows=55,945 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 2 (originally 1) Memory Usage: 4081kB
60. 3,798.897 3,798.897 ↓ 55,945.0 55,945 1

CTE Scan on roletypecd2 b (cost=0.00..0.02 rows=1 width=424) (actual time=3,645.698..3,798.897 rows=55,945 loops=1)

61.          

SubPlan (forHash Full Join)

62. 92.383 184.766 ↑ 1.0 1 92,383

Aggregate (cost=0.09..0.10 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=92,383)

63. 92.383 92.383 ↑ 1.0 6 92,383

Values Scan on "*VALUES*_3" (cost=0.00..0.08 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=92,383)

Planning time : 3.605 ms
Execution time : 8,669.414 ms