explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y1iE

Settings
# exclusive inclusive rows x rows loops node
1. 6,940.097 7,032.480 ↓ 23,095.8 92,383 1

CTE Scan on grouped (cost=30,364.95..30,366.56 rows=4 width=1,408) (actual time=5,291.695..7,032.480 rows=92,383 loops=1)

2.          

CTE contractors

3. 168.363 3,281.479 ↓ 199.9 156,937 1

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

4. 245.915 2,642.305 ↓ 199.9 156,937 1

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

  • Sort Key: cd.pkey, r.roletypecd, r.pkey
  • Sort Method: external merge Disk: 28312kB
5. 87.114 2,396.390 ↓ 199.9 156,937 1

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

6. 246.526 1,995.402 ↓ 199.9 156,937 1

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

7. 51.983 1,435.002 ↓ 199.9 156,937 1

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

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

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

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

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

10. 377.481 946.102 ↓ 199.9 156,937 1

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

  • Hash Cond: ((pd.isactivepartydetail)::text = (p.partydetaillinkid)::text)
11. 17.200 17.200 ↑ 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..17.200 rows=170,426 loops=1)

12. 92.102 551.421 ↓ 199.9 156,937 1

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

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

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

14. 43.520 43.520 ↓ 199.9 156,937 1

Seq Scan on role r (cost=0.00..4,711.52 rows=785 width=32) (actual time=0.110..43.520 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.004 0.008 ↑ 1.0 3 1

Hash (cost=1.15..1.15 rows=3 width=12) (actual time=0.008..0.008 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.001 0.005 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 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
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 470.811 ↑ 1.0 1 156,937

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

25. 313.874 313.874 ↑ 1.0 8 156,937

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

26.          

CTE grouped

27. 565.688 5,916.288 ↓ 23,095.8 92,383 1

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

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

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

  • Sort Key: con.dossierentityid
  • Sort Method: external merge Disk: 24896kB
29. 201.680 5,155.627 ↓ 37,204.2 148,817 1

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

  • Hash Cond: (conadd.partydetailfk = con.pdpkey)
30. 29.708 1,394.473 ↓ 31,958.0 159,790 1

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

  • Filter: (conadd.rn = 1)
  • Rows Removed by Filter: 28937
31. 1,106.354 1,364.765 ↓ 185.9 188,727 1

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

32. 195.164 258.411 ↓ 185.9 188,727 1

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

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

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

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

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

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

CTE Scan on contractors con (cost=0.00..17.66 rows=4 width=3,964) (actual time=2,588.525..3,466.953 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.858 ms
Execution time : 7,305.808 ms