explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HPXM

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 148.925 ↑ 126.0 1 1

Unique (cost=3,158.54..3,160.11 rows=126 width=37) (actual time=148.923..148.925 rows=1 loops=1)

2. 0.017 148.923 ↑ 63.0 2 1

Sort (cost=3,158.54..3,158.85 rows=126 width=37) (actual time=148.923..148.923 rows=2 loops=1)

  • Sort Key: mem.approved, u.email, u.firstname, u.organisationid
  • Sort Method: quicksort Memory: 25kB
3. 0.001 148.906 ↑ 63.0 2 1

Nested Loop (cost=2,287.74..3,154.14 rows=126 width=37) (actual time=82.180..148.906 rows=2 loops=1)

4. 4.802 148.513 ↑ 14.0 1 1

Hash Join (cost=2,287.31..3,147.95 rows=14 width=41) (actual time=81.789..148.513 rows=1 loops=1)

  • Hash Cond: (u.organisationid = o.id)
  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 541
5. 0.140 38.091 ↓ 15.2 547 1

Nested Loop (cost=917.23..1,777.78 rows=36 width=57) (actual time=6.363..38.091 rows=547 loops=1)

6. 0.016 0.016 ↑ 1.0 1 1

Index Scan using pk_request on roadworkrequest r (cost=0.43..8.45 rows=1 width=24) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (id = 1319179)
  • Filter: (roadworkstatustypeid = 8)
7. 0.592 37.935 ↓ 15.2 547 1

Nested Loop (cost=916.80..1,768.97 rows=36 width=41) (actual time=6.346..37.935 rows=547 loops=1)

8. 13.593 23.681 ↓ 3.3 1,518 1

Hash Join (cost=916.52..1,534.04 rows=458 width=41) (actual time=6.308..23.681 rows=1,518 loops=1)

  • Hash Cond: ((mem.user_id)::text = (u.aspmembershipid)::text)
9. 3.820 3.820 ↑ 1.0 10,628 1

Seq Scan on users mem (cost=0.00..546.52 rows=10,628 width=17) (actual time=0.013..3.820 rows=10,628 loops=1)

  • Filter: approved
  • Rows Removed by Filter: 2024
10. 0.467 6.268 ↓ 1.3 1,650 1

Hash (cost=900.94..900.94 rows=1,246 width=77) (actual time=6.268..6.268 rows=1,650 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 201kB
11. 5.801 5.801 ↓ 1.3 1,650 1

Seq Scan on "user" u (cost=0.00..900.94 rows=1,246 width=77) (actual time=0.010..5.801 rows=1,650 loops=1)

  • Filter: (isroadworksinvolvedenabled AND (NOT isdeleted))
  • Rows Removed by Filter: 27244
12. 13.662 13.662 ↓ 0.0 0 1,518

Index Only Scan using user_id_roadworkstatustype_id_unique on useremailnotification n (cost=0.29..0.51 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1,518)

  • Index Cond: ((userid = u.id) AND (roadworkstatustypeid = 8))
  • Heap Fetches: 47
13. 0.953 8.602 ↑ 1.0 5,315 1

Hash (cost=1,303.64..1,303.64 rows=5,315 width=4) (actual time=8.602..8.602 rows=5,315 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 251kB
14. 7.649 7.649 ↑ 1.0 5,315 1

Seq Scan on organisation o (cost=0.00..1,303.64 rows=5,315 width=4) (actual time=0.004..7.649 rows=5,315 loops=1)

  • Filter: emailenabled
  • Rows Removed by Filter: 1349
15.          

SubPlan (forHash Join)

16. 12.466 97.018 ↑ 571.4 7 542

HashAggregate (cost=91.00..131.00 rows=4,000 width=4) (actual time=0.162..0.179 rows=7 loops=542)

  • Group Key: getparentorgtree.id
17. 0.542 84.552 ↑ 571.4 7 542

Append (cost=0.25..81.00 rows=4,000 width=4) (actual time=0.054..0.156 rows=7 loops=542)

18. 29.268 29.268 ↑ 333.3 3 542

Function Scan on getparentorgtree (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.054..0.054 rows=3 loops=542)

19. 20.596 20.596 ↑ 500.0 2 542

Function Scan on getparentorgtree getparentorgtree_1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.038..0.038 rows=2 loops=542)

20. 14.634 14.634 ↓ 0.0 0 542

Function Scan on getparentorgtree getparentorgtree_2 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.027..0.027 rows=0 loops=542)

21. 19.512 19.512 ↑ 500.0 2 542

Function Scan on getparentorgtree getparentorgtree_3 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.036..0.036 rows=2 loops=542)

22. 0.002 0.392 ↑ 4.5 2 1

Materialize (cost=0.43..4.64 rows=9 width=4) (actual time=0.390..0.392 rows=2 loops=1)

23. 0.390 0.390 ↑ 4.5 2 1

Index Only Scan using ix_roadworkrequesthistory on roadworkrequesthistory rrh (cost=0.43..4.59 rows=9 width=4) (actual time=0.388..0.390 rows=2 loops=1)

  • Index Cond: (roadworkrequestid = 1319179)
  • Heap Fetches: 2
Planning time : 0.848 ms