explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JP3K

Settings
# exclusive inclusive rows x rows loops node
1. 6,701.506 6,793.891 ↓ 23,096.2 92,385 1

CTE Scan on grouped (cost=29,320.23..29,321.84 rows=4 width=1,408) (actual time=4,998.816..6,793.891 rows=92,385 loops=1)

2.          

CTE contractors

3. 294.476 3,013.131 ↓ 199.9 156,940 1

WindowAgg (cost=21,605.08..21,734.60 rows=785 width=1,154) (actual time=2,351.158..3,013.131 rows=156,940 loops=1)

4. 231.727 2,404.775 ↓ 199.9 156,940 1

Sort (cost=21,605.08..21,607.04 rows=785 width=664) (actual time=2,351.133..2,404.775 rows=156,940 loops=1)

  • Sort Key: cd.pkey, r.roletypecd, r.pkey
  • Sort Method: external merge Disk: 28296kB
5. 54.548 2,173.048 ↓ 199.9 156,940 1

Nested Loop Left Join (cost=9,676.12..21,567.33 rows=785 width=664) (actual time=518.562..2,173.048 rows=156,940 loops=1)

6. 153.840 1,804.620 ↓ 199.9 156,940 1

Nested Loop Left Join (cost=9,675.83..21,268.36 rows=785 width=648) (actual time=518.549..1,804.620 rows=156,940 loops=1)

7. 50.809 1,336.900 ↓ 199.9 156,940 1

Hash Left Join (cost=9,675.53..17,017.01 rows=785 width=628) (actual time=518.533..1,336.900 rows=156,940 loops=1)

  • Hash Cond: (p.partytypecd = ppt.codeid)
8. 53.159 1,286.086 ↓ 199.9 156,940 1

Hash Left Join (cost=9,674.42..17,012.90 rows=785 width=410) (actual time=518.519..1,286.086 rows=156,940 loops=1)

  • Hash Cond: (pd.gendercd = par_gendercaption.codeid)
9. 55.972 1,232.919 ↓ 199.9 156,940 1

Nested Loop Left Join (cost=9,673.26..17,008.97 rows=785 width=196) (actual time=518.498..1,232.919 rows=156,940 loops=1)

10. 321.033 863.067 ↓ 199.9 156,940 1

Hash Right Join (cost=9,672.84..16,609.06 rows=785 width=196) (actual time=518.479..863.067 rows=156,940 loops=1)

  • Hash Cond: ((pd.isactivepartydetail)::text = (p.partydetaillinkid)::text)
11. 25.701 25.701 ↓ 1.0 170,431 1

Seq Scan on partydetail pd (cost=0.00..6,289.27 rows=170,427 width=91) (actual time=0.128..25.701 rows=170,431 loops=1)

12. 90.107 516.333 ↓ 199.9 156,940 1

Hash (cost=9,663.03..9,663.03 rows=785 width=179) (actual time=516.333..516.333 rows=156,940 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
13. 78.721 426.226 ↓ 199.9 156,940 1

Nested Loop (cost=0.42..9,663.03 rows=785 width=179) (actual time=0.125..426.226 rows=156,940 loops=1)

14. 33.625 33.625 ↓ 199.9 156,940 1

Seq Scan on role r (cost=0.00..4,711.59 rows=785 width=32) (actual time=0.115..33.625 rows=156,940 loops=1)

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

Index Scan using party_pkey on party p (cost=0.42..6.31 rows=1 width=155) (actual time=0.002..0.002 rows=1 loops=156,940)

  • Index Cond: (pkey = r.partyfk)
16. 313.880 313.880 ↑ 1.0 1 156,940

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

  • Index Cond: (pd.partydetailregionfk = pkey)
17. 0.003 0.008 ↓ 3.0 3 1

Hash (cost=1.15..1.15 rows=1 width=222) (actual time=0.008..0.008 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.005 0.005 ↓ 3.0 3 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 9
19. 0.001 0.005 ↓ 2.0 2 1

Hash (cost=1.10..1.10 rows=1 width=222) (actual time=0.005..0.005 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.004 0.004 ↓ 2.0 2 1

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

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

Index Scan using credit_pkey on credit c (cost=0.29..5.42 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=156,940)

  • Index Cond: (pkey = r.creditfk)
22. 313.880 313.880 ↑ 1.0 1 156,940

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

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

SubPlan (forWindowAgg)

24. 0.000 313.880 ↑ 1.0 1 156,940

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

25. 313.880 313.880 ↑ 1.0 8 156,940

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

26.          

CTE grouped

27. 593.152 5,649.420 ↓ 23,096.2 92,385 1

GroupAggregate (cost=7,584.64..7,585.63 rows=4 width=1,140) (actual time=4,998.771..5,649.420 rows=92,385 loops=1)

  • Group Key: con.dossierentityid
28. 188.401 5,056.268 ↓ 37,204.8 148,819 1

Sort (cost=7,584.64..7,584.65 rows=4 width=4,040) (actual time=4,998.746..5,056.268 rows=148,819 loops=1)

  • Sort Key: con.dossierentityid
  • Sort Method: external merge Disk: 24888kB
29. 186.216 4,867.867 ↓ 37,204.8 148,819 1

Hash Right Join (cost=7,254.70..7,584.60 rows=4 width=4,040) (actual time=3,508.362..4,867.867 rows=148,819 loops=1)

  • Hash Cond: (conadd.partydetailfk = con.pdpkey)
30. 26.839 1,392.144 ↓ 31,958.6 159,793 1

Subquery Scan on conadd (cost=7,236.98..7,566.86 rows=5 width=84) (actual time=212.539..1,392.144 rows=159,793 loops=1)

  • Filter: (conadd.rn = 1)
  • Rows Removed by Filter: 28937
31. 1,108.438 1,365.305 ↓ 185.9 188,730 1

WindowAgg (cost=7,236.98..7,554.17 rows=1,015 width=96) (actual time=212.538..1,365.305 rows=188,730 loops=1)

32. 194.007 256.867 ↓ 185.9 188,730 1

Sort (cost=7,236.98..7,239.52 rows=1,015 width=62) (actual time=212.485..256.867 rows=188,730 loops=1)

  • Sort Key: address.partydetailfk, address.addresstypecd, address.pkey
  • Sort Method: external merge Disk: 15336kB
33. 62.860 62.860 ↓ 185.9 188,730 1

Seq Scan on address (cost=0.00..7,186.30 rows=1,015 width=62) (actual time=0.117..62.860 rows=188,730 loops=1)

  • Filter: ((addresstypecd = ANY ('{1,11}'::integer[])) AND ((isactive)::integer = 1))
  • Rows Removed by Filter: 14892
34. 91.262 3,289.507 ↓ 37,082.5 148,330 1

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

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
35. 3,198.245 3,198.245 ↓ 37,082.5 148,330 1

CTE Scan on contractors con (cost=0.00..17.66 rows=4 width=3,964) (actual time=2,351.162..3,198.245 rows=148,330 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 8610
36.          

SubPlan (forCTE Scan)

37. 0.000 92.385 ↑ 1.0 1 92,385

Aggregate (cost=0.05..0.06 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=92,385)

38. 92.385 92.385 ↑ 1.0 3 92,385

Values Scan on "*VALUES*_1" (cost=0.00..0.04 rows=3 width=8) (actual time=0.000..0.001 rows=3 loops=92,385)

Planning time : 2.546 ms
Execution time : 6,822.903 ms