explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bplh

Settings
# exclusive inclusive rows x rows loops node
1. 271.413 271.413 ↑ 1.8 26,248 1

CTE Scan on normalised_data (cost=18,461.33..19,396.61 rows=46,764 width=69) (actual time=175.239..271.413 rows=26,248 loops=1)

2.          

CTE trial_orgs

3. 0.006 0.070 ↑ 1.3 23 1

Nested Loop (cost=0.28..13.74 rows=29 width=8) (actual time=0.042..0.070 rows=23 loops=1)

4. 0.017 0.017 ↑ 2.0 1 1

Seq Scan on extended_functions ext_funcs (cost=0.00..3.70 rows=2 width=4) (actual time=0.011..0.017 rows=1 loops=1)

  • Filter: ((role_name)::text = ANY ('{TRIAL_PUB_TM,CM_TRIAL}'::text[]))
  • Rows Removed by Filter: 44
5. 0.047 0.047 ↑ 1.2 23 1

Index Only Scan using organisation_extended_funcs_pkey on organisation_extended_funcs org_ext_func (cost=0.28..4.75 rows=27 width=8) (actual time=0.028..0.047 rows=23 loops=1)

  • Index Cond: (extended_function_id = ext_funcs.extended_function_id)
  • Heap Fetches: 23
6.          

CTE users_dupes

7. 1.914 79.667 ↑ 1.6 31,280 1

Append (cost=2,738.85..9,280.75 rows=48,529 width=25) (actual time=29.677..79.667 rows=31,280 loops=1)

8. 6.462 42.506 ↓ 1.0 24,488 1

Hash Join (cost=2,738.85..4,038.49 rows=24,254 width=25) (actual time=29.676..42.506 rows=24,488 loops=1)

  • Hash Cond: ((d.username)::text = (u.username)::text)
9. 6.778 6.778 ↑ 1.0 25,000 1

Index Scan using raw_data_pkey on raw_data d (cost=0.43..1,233.01 rows=25,543 width=17) (actual time=0.080..6.778 rows=25,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 25000))
10. 15.645 29.266 ↑ 1.0 43,836 1

Hash (cost=2,190.41..2,190.41 rows=43,841 width=32) (actual time=29.266..29.266 rows=43,836 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3284kB
11. 13.621 13.621 ↑ 1.0 43,841 1

Seq Scan on users u (cost=0.00..2,190.41 rows=43,841 width=32) (actual time=0.012..13.621 rows=43,841 loops=1)

12. 4.352 35.247 ↑ 3.6 6,792 1

Hash Join (cost=2,738.85..4,756.97 rows=24,275 width=25) (actual time=24.917..35.247 rows=6,792 loops=1)

  • Hash Cond: ((d_1.username)::text = (u_1.email)::text)
13. 6.305 6.305 ↑ 1.0 25,000 1

Index Scan using raw_data_pkey on raw_data d_1 (cost=0.43..1,233.01 rows=25,543 width=17) (actual time=0.046..6.305 rows=25,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 25000))
14. 11.391 24.590 ↑ 1.0 42,083 1

Hash (cost=2,190.41..2,190.41 rows=43,841 width=34) (actual time=24.590..24.590 rows=42,083 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3246kB
15. 13.199 13.199 ↑ 1.0 43,841 1

Seq Scan on users u_1 (cost=0.00..2,190.41 rows=43,841 width=34) (actual time=0.007..13.199 rows=43,841 loops=1)

16.          

CTE users

17. 8.934 97.133 ↑ 11.6 417 1

HashAggregate (cost=1,334.55..1,383.08 rows=4,853 width=138) (actual time=97.029..97.133 rows=417 loops=1)

  • Group Key: users_dupes.username, users_dupes.user_id, users_dupes.organisation_id
18. 88.199 88.199 ↑ 1.6 31,280 1

CTE Scan on users_dupes (cost=0.00..970.58 rows=48,529 width=138) (actual time=29.679..88.199 rows=31,280 loops=1)

19.          

CTE normalised_data

20. 65.742 260.842 ↑ 1.8 26,248 1

Hash Left Join (cost=3,967.95..7,783.77 rows=46,764 width=42) (actual time=175.236..260.842 rows=26,248 loops=1)

  • Hash Cond: (((d_2.scope)::text = (scd.scope)::text) AND ((d_2.context)::text = (scd.context)::text))
21. 8.284 195.091 ↑ 1.8 26,248 1

Hash Left Join (cost=3,966.78..4,953.38 rows=46,764 width=53) (actual time=175.192..195.091 rows=26,248 loops=1)

  • Hash Cond: (((d_2.operation)::text = (opd.operation)::text) AND ((d_2.op_description)::text = (opd.description)::text))
22. 7.673 186.789 ↑ 1.8 26,248 1

Merge Right Join (cost=3,965.63..4,691.35 rows=46,764 width=74) (actual time=175.161..186.789 rows=26,248 loops=1)

  • Merge Cond: ((u_2.username)::text = (d_2.username)::text)
23. 0.728 99.020 ↑ 10.9 447 1

Sort (cost=536.91..549.04 rows=4,853 width=146) (actual time=98.973..99.020 rows=447 loops=1)

  • Sort Key: u_2.username
  • Sort Method: quicksort Memory: 60kB
24. 0.068 98.292 ↑ 10.9 447 1

Hash Left Join (cost=67.71..239.79 rows=4,853 width=146) (actual time=97.848..98.292 rows=447 loops=1)

  • Hash Cond: (org.organisation_id = org_r.organisation_id)
25. 0.081 98.138 ↑ 10.9 447 1

Hash Left Join (cost=66.76..219.05 rows=4,853 width=146) (actual time=97.752..98.138 rows=447 loops=1)

  • Hash Cond: (org.region_id = region.region_id)
26. 0.098 97.588 ↑ 11.6 417 1

Hash Left Join (cost=37.82..147.69 rows=4,853 width=146) (actual time=97.273..97.588 rows=417 loops=1)

  • Hash Cond: (u_2.organisation_id = org.organisation_id)
27. 97.258 97.258 ↑ 11.6 417 1

CTE Scan on users u_2 (cost=0.00..97.06 rows=4,853 width=138) (actual time=97.031..97.258 rows=417 loops=1)

28. 0.083 0.232 ↑ 1.3 676 1

Hash (cost=26.81..26.81 rows=881 width=8) (actual time=0.232..0.232 rows=676 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
29. 0.149 0.149 ↑ 1.3 676 1

Seq Scan on organisation org (cost=0.00..26.81 rows=881 width=8) (actual time=0.008..0.149 rows=676 loops=1)

30. 0.102 0.469 ↓ 2.4 716 1

Hash (cost=25.22..25.22 rows=298 width=8) (actual time=0.469..0.469 rows=716 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
31. 0.137 0.367 ↓ 2.4 716 1

Hash Right Join (cost=17.70..25.22 rows=298 width=8) (actual time=0.153..0.367 rows=716 loops=1)

  • Hash Cond: (r_la.region_id = region.region_id)
32. 0.086 0.086 ↓ 2.7 748 1

Seq Scan on region_la r_la (cost=0.00..6.79 rows=279 width=8) (actual time=0.004..0.086 rows=748 loops=1)

33. 0.065 0.144 ↓ 1.5 461 1

Hash (cost=13.97..13.97 rows=298 width=4) (actual time=0.144..0.144 rows=461 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
34. 0.079 0.079 ↓ 1.5 461 1

Seq Scan on region (cost=0.00..13.97 rows=298 width=4) (actual time=0.004..0.079 rows=461 loops=1)

  • Filter: (NOT national_coverage)
  • Rows Removed by Filter: 36
35. 0.007 0.086 ↑ 1.3 23 1

Hash (cost=0.58..0.58 rows=29 width=8) (actual time=0.086..0.086 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.079 0.079 ↑ 1.3 23 1

CTE Scan on trial_orgs org_r (cost=0.00..0.58 rows=29 width=8) (actual time=0.046..0.079 rows=23 loops=1)

37. 61.077 80.096 ↓ 1.0 26,248 1

Sort (cost=3,428.72..3,492.58 rows=25,543 width=75) (actual time=76.181..80.096 rows=26,248 loops=1)

  • Sort Key: d_2.username
  • Sort Method: external sort Disk: 2264kB
38. 6.990 19.019 ↑ 1.0 25,000 1

Hash Left Join (cost=2.74..1,558.89 rows=25,543 width=75) (actual time=0.090..19.019 rows=25,000 loops=1)

  • Hash Cond: (((d_2.object_type)::text = (objd.object_type)::text) AND ((d_2.object_description)::text = (objd.object_description)::text))
39. 5.445 12.018 ↑ 1.0 25,000 1

Hash Left Join (cost=1.52..1,339.01 rows=25,543 width=91) (actual time=0.068..12.018 rows=25,000 loops=1)

  • Hash Cond: ((d_2.app)::text = (appd.app)::text)
40. 6.564 6.564 ↑ 1.0 25,000 1

Index Scan using raw_data_pkey on raw_data d_2 (cost=0.43..1,233.01 rows=25,543 width=90) (actual time=0.047..6.564 rows=25,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 25000))
41. 0.005 0.009 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=86) (actual time=0.009..0.009 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on app_dim appd (cost=0.00..1.04 rows=4 width=86) (actual time=0.003..0.004 rows=4 loops=1)

43. 0.006 0.011 ↑ 1.3 7 1

Hash (cost=1.09..1.09 rows=9 width=408) (actual time=0.011..0.011 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.005 0.005 ↑ 1.0 9 1

Seq Scan on object_dim objd (cost=0.00..1.09 rows=9 width=408) (actual time=0.003..0.005 rows=9 loops=1)

45. 0.008 0.018 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=408) (actual time=0.018..0.018 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.010 0.010 ↑ 1.0 6 1

Seq Scan on operation_dim opd (cost=0.00..1.06 rows=6 width=408) (actual time=0.008..0.010 rows=6 loops=1)

47. 0.006 0.009 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=264) (actual time=0.009..0.009 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.003 0.003 ↑ 1.0 7 1

Seq Scan on scope_dim scd (cost=0.00..1.07 rows=7 width=264) (actual time=0.002..0.003 rows=7 loops=1)

Planning time : 3.703 ms
Execution time : 276.043 ms