explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gqa6

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 10.793 402.038 ↓ 0.0 0 1

Insert on usage_fact (cost=20,565.71..20,799.51 rows=9,352 width=41) (actual time=402.038..402.038 rows=0 loops=1)

2.          

CTE trial_orgs

3. 0.007 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.046 0.046 ↑ 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.046 rows=23 loops=1)

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

CTE users_dupes

7. 2.799 103.697 ↑ 1.6 31,280 1

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

8. 10.754 54.962 ↓ 1.0 24,488 1

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

  • Hash Cond: ((d.username)::text = (u.username)::text)
9. 11.192 11.192 ↑ 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.096..11.192 rows=25,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 25000))
10. 17.973 33.016 ↑ 1.0 43,837 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3284kB
11. 15.043 15.043 ↓ 1.0 43,842 1

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

12. 5.963 45.936 ↑ 3.6 6,792 1

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

  • Hash Cond: ((d_1.username)::text = (u_1.email)::text)
13. 8.317 8.317 ↑ 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.041..8.317 rows=25,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 25000))
14. 14.594 31.656 ↑ 1.0 42,084 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3246kB
15. 17.062 17.062 ↓ 1.0 43,842 1

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

16.          

CTE users

17. 14.106 130.601 ↑ 11.6 417 1

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

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

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

19.          

CTE normalised_data

20. 89.521 352.625 ↑ 1.8 26,248 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

28. 0.099 0.242 ↑ 1.3 676 1

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

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

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

30. 0.097 0.462 ↓ 2.4 716 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
31. 0.140 0.365 ↓ 2.4 716 1

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

  • Hash Cond: (r_la.region_id = region.region_id)
32. 0.088 0.088 ↓ 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.088 rows=748 loops=1)

33. 0.060 0.137 ↓ 1.5 461 1

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

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

Seq Scan on region (cost=0.00..13.97 rows=298 width=4) (actual time=0.003..0.077 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. 88.517 109.370 ↓ 1.0 26,248 1

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

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

Hash Left Join (cost=2.74..1,558.89 rows=25,543 width=75) (actual time=0.091..20.853 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. 6.043 13.302 ↑ 1.0 25,000 1

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

  • Hash Cond: ((d_2.app)::text = (appd.app)::text)
40. 7.250 7.250 ↑ 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.046..7.250 rows=25,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 25000))
41. 0.004 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.005 0.005 ↑ 1.0 4 1

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

43. 0.006 0.010 ↑ 1.3 7 1

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

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

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

45. 0.008 0.015 ↑ 1.0 6 1

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

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

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

47. 0.005 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.004 0.004 ↑ 1.0 7 1

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

49. 0.731 391.245 ↑ 4.1 2,293 1

Subquery Scan on *SELECT* (cost=2,104.38..2,338.18 rows=9,352 width=41) (actual time=388.389..391.245 rows=2,293 loops=1)

50. 23.070 390.514 ↑ 4.1 2,293 1

HashAggregate (cost=2,104.38..2,221.28 rows=9,352 width=69) (actual time=388.384..390.514 rows=2,293 loops=1)

  • Group Key: normalised_data.trial, normalised_data.user_id, normalised_data.org_id, normalised_data.date_id, normalised_data.app_id, normalised_data.operation_id, normalised_data.object_id, normalised_data.local_authority_id, normalised_data.scope_id
51. 367.444 367.444 ↑ 1.8 26,248 1

CTE Scan on normalised_data (cost=0.00..935.28 rows=46,764 width=69) (actual time=235.768..367.444 rows=26,248 loops=1)

Planning time : 4.090 ms
Execution time : 646.869 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint usage_fact_date_id_fkey 42.148 ms 2293 0.018 ms
for constraint usage_fact_object_id_fkey 24.431 ms 2293 0.011 ms
for constraint usage_fact_app_dim_id_fk 10.376 ms 2293 0.005 ms
for constraint usage_fact_operation_dim_id_fk 26.075 ms 2293 0.011 ms
for constraint usage_fact_scope_dim_id_fk 29.941 ms 2293 0.013 ms
for constraint usage_fact_local_authority_dim_id_fk 22.732 ms 2293 0.010 ms
for constraint usage_fact_organisation_dim_id_fk 39.143 ms 2293 0.017 ms
for constraint usage_fact_user_dim_id_fk 44.451 ms 2293 0.019 ms