explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pfIW

Settings
# exclusive inclusive rows x rows loops node
1. 481.179 481.179 ↑ 3.7 50,000 1

CTE Scan on normalised_data (cost=37,490.62..41,233.14 rows=187,126 width=56) (actual time=336.457..481.179 rows=50,000 loops=1)

2.          

CTE users_dupes

3. 4.329 96.762 ↑ 1.5 65,267 1

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

4. 12.957 47.487 ↑ 1.0 48,026 1

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

  • Hash Cond: ((d.username)::text = (u.username)::text)
5. 13.627 13.627 ↑ 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.040..13.627 rows=50,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
6. 11.210 20.903 ↓ 1.0 43,836 1

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

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

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

8. 9.451 44.946 ↑ 2.8 17,241 1

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

  • Hash Cond: ((d_1.username)::text = (u_1.email)::text)
9. 12.811 12.811 ↑ 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.040..12.811 rows=50,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
10. 10.192 22.684 ↑ 1.0 42,083 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3246kB
11. 12.492 12.492 ↓ 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.009..12.492 rows=43,841 loops=1)

12.          

CTE users

13. 20.037 139.283 ↑ 10.9 892 1

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

  • Group Key: users_dupes.username, users_dupes.user_id, users_dupes.organisation_id
14. 119.246 119.246 ↑ 1.5 65,267 1

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

15.          

CTE normalised_data

16. 118.472 461.819 ↑ 3.7 50,000 1

Merge Right Join (cost=8,508.53..21,655.89 rows=187,126 width=29) (actual time=336.452..461.819 rows=50,000 loops=1)

  • Merge Cond: ((u_2.username)::text = (d_2.username)::text)
17. 1.798 141.337 ↑ 10.9 892 1

Sort (cost=837.07..861.34 rows=9,708 width=130) (actual time=141.220..141.337 rows=892 loops=1)

  • Sort Key: u_2.username
  • Sort Method: quicksort Memory: 89kB
18. 139.539 139.539 ↑ 10.9 892 1

CTE Scan on users u_2 (cost=0.00..194.16 rows=9,708 width=130) (actual time=139.094..139.539 rows=892 loops=1)

19. 130.519 202.010 ↑ 1.0 50,000 1

Sort (cost=7,671.46..7,799.20 rows=51,095 width=46) (actual time=195.202..202.010 rows=50,000 loops=1)

  • Sort Key: d_2.username
  • Sort Method: external sort Disk: 2832kB
20. 15.145 71.491 ↑ 1.0 50,000 1

Hash Left Join (cost=5.07..3,675.60 rows=51,095 width=46) (actual time=0.126..71.491 rows=50,000 loops=1)

  • Hash Cond: (((d_2.scope)::text = (scd.scope)::text) AND ((d_2.context)::text = (scd.context)::text))
21. 15.494 56.337 ↑ 1.0 50,000 1

Hash Left Join (cost=3.89..3,393.40 rows=51,095 width=54) (actual time=0.108..56.337 rows=50,000 loops=1)

  • Hash Cond: (((d_2.operation)::text = (opd.operation)::text) AND ((d_2.op_description)::text = (opd.description)::text))
22. 15.925 40.834 ↑ 1.0 50,000 1

Hash Left Join (cost=2.74..3,107.22 rows=51,095 width=75) (actual time=0.087..40.834 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))
23. 10.967 24.898 ↑ 1.0 50,000 1

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

  • Hash Cond: ((d_2.app)::text = (appd.app)::text)
24. 13.919 13.919 ↑ 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.048..13.919 rows=50,000 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 50000))
25. 0.006 0.012 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.006 0.006 ↑ 1.0 4 1

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

27. 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
28. 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)

29. 0.006 0.009 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.003 0.003 ↑ 1.0 6 1

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

31. 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
32. 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 : 1.836 ms
Execution time : 487.835 ms