explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dcPX

Settings
# exclusive inclusive rows x rows loops node
1. 501.126 501.126 ↑ 3.4 54,967 1

CTE Scan on normalised_data (cost=41,623.09..45,365.61 rows=187,126 width=69) (actual time=286.729..501.126 rows=54,967 loops=1)

2.          

CTE trial_orgs

3. 0.006 0.069 ↑ 1.3 23 1

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

4. 0.016 0.016 ↑ 2.0 1 1

Seq Scan on extended_functions ext_funcs (cost=0.00..3.70 rows=2 width=4) (actual time=0.010..0.016 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.029..0.047 rows=23 loops=1)

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

CTE users_dupes

7. 4.228 96.147 ↑ 1.5 65,267 1

Append (cost=2,738.67..13,068.06 rows=97,076 width=25) (actual time=21.516..96.147 rows=65,267 loops=1)

8. 13.055 47.549 ↑ 1.0 48,026 1

Hash Join (cost=2,738.67..5,330.04 rows=48,517 width=25) (actual time=21.516..47.549 rows=48,026 loops=1)

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

Index Scan using raw_data_pkey on raw_data d (cost=0.43..2,457.66 rows=51,095 width=17) (actual time=0.065..13.296 rows=50,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
10. 11.161 21.198 ↓ 1.0 43,836 1

Hash (cost=2,190.33..2,190.33 rows=43,833 width=32) (actual time=21.198..21.198 rows=43,836 loops=1)

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

Seq Scan on users u (cost=0.00..2,190.33 rows=43,833 width=32) (actual time=0.007..10.037 rows=43,841 loops=1)

12. 9.307 44.370 ↑ 2.8 17,241 1

Hash Join (cost=2,738.67..6,767.26 rows=48,559 width=25) (actual time=22.600..44.370 rows=17,241 loops=1)

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

Index Scan using raw_data_pkey on raw_data d_1 (cost=0.43..2,457.66 rows=51,095 width=17) (actual time=0.039..12.773 rows=50,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
14. 10.008 22.290 ↑ 1.0 42,083 1

Hash (cost=2,190.33..2,190.33 rows=43,833 width=34) (actual time=22.290..22.290 rows=42,083 loops=1)

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

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

16.          

CTE users

17. 19.309 137.197 ↑ 10.9 892 1

HashAggregate (cost=2,669.59..2,766.67 rows=9,708 width=138) (actual time=137.011..137.197 rows=892 loops=1)

  • Group Key: users_dupes.username, users_dupes.user_id, users_dupes.organisation_id
18. 117.888 117.888 ↑ 1.5 65,267 1

CTE Scan on users_dupes (cost=0.00..1,941.52 rows=97,076 width=138) (actual time=21.518..117.888 rows=65,267 loops=1)

19.          

CTE normalised_data

20. 139.771 473.313 ↑ 3.4 54,967 1

Hash Left Join (cost=10,426.47..25,774.63 rows=187,126 width=42) (actual time=286.726..473.313 rows=54,967 loops=1)

  • Hash Cond: (((d_2.scope)::text = (scd.scope)::text) AND ((d_2.context)::text = (scd.context)::text))
21. 17.626 333.534 ↑ 3.4 54,967 1

Hash Left Join (cost=10,425.29..14,452.33 rows=187,126 width=53) (actual time=286.690..333.534 rows=54,967 loops=1)

  • Hash Cond: (((d_2.operation)::text = (opd.operation)::text) AND ((d_2.op_description)::text = (opd.description)::text))
22. 16.440 315.894 ↑ 3.4 54,967 1

Merge Right Join (cost=10,424.14..13,407.31 rows=187,126 width=74) (actual time=286.665..315.894 rows=54,967 loops=1)

  • Merge Cond: ((u_2.username)::text = (d_2.username)::text)
23. 1.838 140.441 ↑ 10.0 971 1

Sort (cost=1,049.56..1,073.83 rows=9,708 width=146) (actual time=140.327..140.441 rows=971 loops=1)

  • Sort Key: u_2.username
  • Sort Method: quicksort Memory: 102kB
24. 0.218 138.603 ↑ 10.0 971 1

Hash Left Join (cost=79.00..406.65 rows=9,708 width=146) (actual time=138.001..138.603 rows=971 loops=1)

  • Hash Cond: (u_2.organisation_id = org.organisation_id)
25. 137.406 137.406 ↑ 10.9 892 1

CTE Scan on users u_2 (cost=0.00..194.16 rows=9,708 width=138) (actual time=137.012..137.406 rows=892 loops=1)

26. 0.102 0.979 ↑ 1.3 697 1

Hash (cost=67.99..67.99 rows=881 width=12) (actual time=0.979..0.979 rows=697 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
27. 0.100 0.877 ↑ 1.3 697 1

Hash Left Join (cost=29.88..67.99 rows=881 width=12) (actual time=0.556..0.877 rows=697 loops=1)

  • Hash Cond: (org.organisation_id = org_r.organisation_id)
28. 0.175 0.694 ↑ 1.3 697 1

Hash Left Join (cost=28.94..63.46 rows=881 width=8) (actual time=0.465..0.694 rows=697 loops=1)

  • Hash Cond: (org.region_id = region.region_id)
29. 0.067 0.067 ↑ 1.3 676 1

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

30. 0.097 0.452 ↓ 2.4 716 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
31. 0.136 0.355 ↓ 2.4 716 1

Hash Right Join (cost=17.70..25.22 rows=298 width=8) (actual time=0.142..0.355 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.003..0.086 rows=748 loops=1)

33. 0.060 0.133 ↓ 1.5 461 1

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

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

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

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

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

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

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

37. 5.191 159.013 ↓ 1.1 54,967 1

Materialize (cost=9,374.58..9,630.05 rows=51,095 width=75) (actual time=146.331..159.013 rows=54,967 loops=1)

38. 114.960 153.822 ↑ 1.0 50,000 1

Sort (cost=9,374.58..9,502.32 rows=51,095 width=75) (actual time=146.324..153.822 rows=50,000 loops=1)

  • Sort Key: d_2.username
  • Sort Method: external merge Disk: 4448kB
39. 14.714 38.862 ↑ 1.0 50,000 1

Hash Left Join (cost=2.74..3,107.22 rows=51,095 width=75) (actual time=0.076..38.862 rows=50,000 loops=1)

  • Hash Cond: (((d_2.object_type)::text = (objd.object_type)::text) AND ((d_2.object_description)::text = (objd.object_description)::text))
40. 11.115 24.138 ↑ 1.0 50,000 1

Hash Left Join (cost=1.52..2,668.61 rows=51,095 width=91) (actual time=0.058..24.138 rows=50,000 loops=1)

  • Hash Cond: ((d_2.app)::text = (appd.app)::text)
41. 13.014 13.014 ↑ 1.0 50,000 1

Index Scan using raw_data_pkey on raw_data d_2 (cost=0.43..2,457.66 rows=51,095 width=90) (actual time=0.041..13.014 rows=50,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
42. 0.006 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
43. 0.003 0.003 ↑ 1.0 4 1

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

44. 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
45. 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.003..0.004 rows=9 loops=1)

46. 0.006 0.014 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.008 0.008 ↑ 1.0 6 1

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

48. 0.005 0.008 ↑ 1.0 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 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.003..0.003 rows=7 loops=1)

Planning time : 2.204 ms
Execution time : 510.046 ms