explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fJRx

Settings
# exclusive inclusive rows x rows loops node
1. 6,636.105 6,728.488 ↓ 23,095.8 92,383 1

CTE Scan on grouped (cost=30,366.45..30,368.06 rows=4 width=1,408) (actual time=5,040.936..6,728.488 rows=92,383 loops=1)

2.          

CTE contractors

3. 277.545 3,042.325 ↓ 199.9 156,937 1

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

4. 234.718 2,450.906 ↓ 199.9 156,937 1

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

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

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

6. 202.428 1,855.274 ↓ 199.9 156,937 1

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

7. 50.068 1,338.972 ↓ 199.9 156,937 1

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

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

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

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

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

10. 312.842 871.837 ↓ 199.9 156,937 1

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

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

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

12. 93.685 542.305 ↓ 199.9 156,937 1

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

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

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

14. 44.281 44.281 ↓ 199.9 156,937 1

Seq Scan on role r (cost=0.00..4,711.52 rows=785 width=32) (actual time=0.123..44.281 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 grouped

27. 551.598 5,648.990 ↓ 23,095.8 92,383 1

GroupAggregate (cost=7,584.36..7,585.35 rows=4 width=1,140) (actual time=5,040.892..5,648.990 rows=92,383 loops=1)

  • Group Key: con.dossierentityid
28. 190.394 5,097.392 ↓ 37,204.2 148,817 1

Sort (cost=7,584.36..7,584.37 rows=4 width=4,040) (actual time=5,040.868..5,097.392 rows=148,817 loops=1)

  • Sort Key: con.dossierentityid
  • Sort Method: external merge Disk: 24896kB
29. 193.112 4,906.998 ↓ 37,204.2 148,817 1

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

  • Hash Cond: (conadd.partydetailfk = con.pdpkey)
30. 25.371 1,403.731 ↓ 31,958.2 159,791 1

Subquery Scan on conadd (cost=7,236.70..7,566.58 rows=5 width=84) (actual time=214.474..1,403.731 rows=159,791 loops=1)

  • Filter: (conadd.rn = 1)
  • Rows Removed by Filter: 28937
31. 1,124.671 1,378.360 ↓ 185.9 188,728 1

WindowAgg (cost=7,236.70..7,553.89 rows=1,015 width=96) (actual time=214.473..1,378.360 rows=188,728 loops=1)

32. 188.519 253.689 ↓ 185.9 188,728 1

Sort (cost=7,236.70..7,239.24 rows=1,015 width=62) (actual time=214.416..253.689 rows=188,728 loops=1)

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

Seq Scan on address (cost=0.00..7,186.02 rows=1,015 width=62) (actual time=0.109..65.170 rows=188,728 loops=1)

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

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

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

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

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

SubPlan (forCTE Scan)

37. 0.000 92.383 ↑ 1.0 1 92,383

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

38. 92.383 92.383 ↑ 1.0 3 92,383

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

Planning time : 2.935 ms
Execution time : 6,755.266 ms