explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3svG

Settings
# exclusive inclusive rows x rows loops node
1. 0.130 178.883 ↑ 3.2 62 1

Sort (cost=314,374.10..314,374.60 rows=201 width=1,090) (actual time=178.881..178.883 rows=62 loops=1)

  • Sort Key: org.sortstring2
  • Sort Method: quicksort Memory: 41kB
2. 0.162 178.753 ↑ 3.2 62 1

HashAggregate (cost=314,364.40..314,366.41 rows=201 width=1,090) (actual time=178.734..178.753 rows=62 loops=1)

  • Group Key: org.id, org.name, org.parentid, org.organisationroleid, org.iteration, org.sortstring, org.sortstring2, org.isactive, (true), (0), (0)
3. 0.016 178.591 ↑ 3.2 62 1

Append (cost=0.28..314,358.87 rows=201 width=1,090) (actual time=0.013..178.591 rows=62 loops=1)

4. 0.013 0.013 ↑ 1.0 1 1

Index Scan using ix_parent on organisation org (cost=0.28..8.30 rows=1 width=159) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (parentid IS NULL)
5. 0.025 178.562 ↑ 3.3 61 1

Subquery Scan on *SELECT* 2 (cost=175,183.03..314,350.56 rows=200 width=162) (actual time=48.984..178.562 rows=61 loops=1)

6. 0.434 178.537 ↑ 3.3 61 1

Subquery Scan on t2 (cost=175,183.03..314,348.56 rows=200 width=162) (actual time=48.983..178.537 rows=61 loops=1)

7. 1.114 178.103 ↑ 3.3 61 1

Hash Join (cost=175,183.03..314,344.06 rows=200 width=159) (actual time=48.977..178.103 rows=61 loops=1)

  • Hash Cond: (og.id = o.id)
8. 0.613 0.613 ↑ 1.0 6,372 1

Seq Scan on organisation og (cost=0.00..607.72 rows=6,372 width=150) (actual time=0.009..0.613 rows=6,372 loops=1)

9. 0.018 34.917 ↑ 3.3 61 1

Hash (cost=175,180.53..175,180.53 rows=200 width=8) (actual time=34.917..34.917 rows=61 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
10. 0.008 34.899 ↑ 3.3 61 1

Unique (cost=175,177.03..175,178.53 rows=200 width=8) (actual time=34.890..34.899 rows=61 loops=1)

11. 0.019 34.891 ↑ 3.3 61 1

Sort (cost=175,177.03..175,177.53 rows=200 width=8) (actual time=34.889..34.891 rows=61 loops=1)

  • Sort Key: o.id, ((count(psm.rwid))::integer)
  • Sort Method: quicksort Memory: 27kB
12. 0.286 34.872 ↑ 3.3 61 1

HashAggregate (cost=175,166.88..175,169.38 rows=200 width=8) (actual time=34.862..34.872 rows=61 loops=1)

  • Group Key: o.id
13. 0.356 34.586 ↑ 9,059.8 769 1

Nested Loop (cost=27.33..140,331.88 rows=6,967,000 width=12) (actual time=1.952..34.586 rows=769 loops=1)

14. 0.323 8.350 ↑ 10.8 647 1

Hash Right Join (cost=27.08..991.63 rows=6,967 width=12) (actual time=1.304..8.350 rows=647 loops=1)

  • Hash Cond: (psm.ownerid = tempp.id)
15. 1.834 7.884 ↑ 42.7 350 1

Hash Join (cost=17.25..856.05 rows=14,955 width=12) (actual time=1.155..7.884 rows=350 loops=1)

  • Hash Cond: (psm.psmid = orgtree2.id)
16. 5.012 5.012 ↓ 1.0 29,913 1

Seq Scan on psmreadyforexportrw psm (cost=0.00..593.91 rows=29,910 width=16) (actual time=0.023..5.012 rows=29,913 loops=1)

  • Filter: (lasthistorydate < '2019-04-07 00:00:00'::timestamp without time zone)
17. 0.002 1.038 ↑ 33.3 6 1

Hash (cost=14.75..14.75 rows=200 width=4) (actual time=1.038..1.038 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.008 1.036 ↑ 33.3 6 1

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=1.033..1.036 rows=6 loops=1)

  • Group Key: orgtree2.id
19. 1.028 1.028 ↑ 166.7 6 1

Function Scan on getorgtree orgtree2 (cost=0.25..10.25 rows=1,000 width=4) (actual time=1.027..1.028 rows=6 loops=1)

20. 0.045 0.143 ↑ 1.0 348 1

Hash (cost=5.48..5.48 rows=348 width=4) (actual time=0.143..0.143 rows=348 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
21. 0.098 0.098 ↑ 1.0 348 1

Seq Scan on tempownerlist tempp (cost=0.00..5.48 rows=348 width=4) (actual time=0.036..0.098 rows=348 loops=1)

22. 25.880 25.880 ↑ 1,000.0 1 647

Function Scan on getparentorgtree o (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.040..0.040 rows=1 loops=647)

23.          

SubPlan (forHash Join)

24. 0.488 141.459 ↑ 1.0 1 61

Aggregate (cost=692.67..692.68 rows=1 width=4) (actual time=2.319..2.319 rows=1 loops=61)

25. 1.392 140.971 ↓ 8.5 111 61

Nested Loop (cost=17.25..692.64 rows=13 width=0) (actual time=0.759..2.311 rows=111 loops=61)

26. 1.342 1.342 ↑ 1.0 1 61

Seq Scan on tempownerlist t2_1 (cost=0.00..6.35 rows=1 width=4) (actual time=0.011..0.022 rows=1 loops=61)

  • Filter: (id = og.id)
  • Rows Removed by Filter: 347
27. 1.463 138.237 ↓ 8.8 115 59

Hash Join (cost=17.25..686.16 rows=13 width=4) (actual time=0.754..2.343 rows=115 loops=59)

  • Hash Cond: (p.psmid = orgtree1.id)
28. 126.437 126.437 ↓ 4.4 115 59

Seq Scan on psmreadyforexportrw p (cost=0.00..668.69 rows=26 width=8) (actual time=0.575..2.143 rows=115 loops=59)

  • Filter: ((lasthistorydate < '2019-03-07 00:00:00'::timestamp without time zone) AND (ownerid = og.id))
  • Rows Removed by Filter: 29798
29. 0.935 10.337 ↓ 31.9 6,372 1

Hash (cost=14.75..14.75 rows=200 width=4) (actual time=10.337..10.337 rows=6,372 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 289kB
30. 3.006 9.402 ↓ 31.9 6,372 1

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=7.913..9.402 rows=6,372 loops=1)

  • Group Key: orgtree1.id
31. 6.396 6.396 ↓ 6.4 6,372 1

Function Scan on getorgtree orgtree1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=5.610..6.396 rows=6,372 loops=1)

Planning time : 117.953 ms