explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ePxW : Optimization for: plan #Kpqk

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,811.311 52,044.378 ↑ 705.0 35,692 1

Unique (cost=15,108,937.36..15,800,904.13 rows=25,162,428 width=1,224) (actual time=48,732.660..52,044.378 rows=35,692 loops=1)

2. 34,412.317 50,233.067 ↑ 3.4 7,472,315 1

Sort (cost=15,108,937.36..15,171,843.43 rows=25,162,428 width=1,224) (actual time=48,732.657..50,233.067 rows=7,472,315 loops=1)

  • Sort Key: a11.access_status_id, a11.access_status_desc, access_transactions.tran_date_est, (CASE WHEN ((date_part('dow'::text, (access_transactions.tran_date_est)::timestamp without time zone) + '1'::double precision) = '1'::double precision) THEN '7'::double precision ELSE ((date_part('dow'::text, (access_transactions.tran_date_est)::timestamp without time zone) + '1'::double precision) - '1'::double precision) END), a19.day_of_week_desc, (substr((a19.day_of_week_desc)::text, 1, 3)), a14.directorate_id, a18.jde_emp_directory_desc, a16.hour_id, a17.hour_desc
  • Sort Method: external merge Disk: 544608kB
3. 2,130.471 15,820.750 ↑ 3.4 7,472,315 1

Merge Join (cost=2,823,702.91..4,349,197.11 rows=25,162,428 width=1,224) (actual time=11,517.663..15,820.750 rows=7,472,315 loops=1)

  • Merge Cond: (((a19.day_of_week_id)::double precision) = (CASE WHEN ((date_part('dow'::text, (access_transactions.tran_date_est)::timestamp without time zone) + '1'::double precision) = '1'::double precision) THEN '7'::double precision ELSE ((date_part('dow'::text, (access_transactions.tran_date_est)::timestamp without time zone) + '1'::double precision) - '1'::double precision) END))
4. 0.013 0.030 ↑ 114.3 7 1

Sort (cost=56.58..58.58 rows=800 width=70) (actual time=0.025..0.030 rows=7 loops=1)

  • Sort Key: ((a19.day_of_week_id)::double precision)
  • Sort Method: quicksort Memory: 25kB
5. 0.017 0.017 ↑ 114.3 7 1

Seq Scan on day_of_week a19 (cost=0.00..18.00 rows=800 width=70) (actual time=0.015..0.017 rows=7 loops=1)

6. 868.292 13,690.249 ↓ 1.2 7,472,315 1

Materialize (cost=2,823,646.34..2,855,099.37 rows=6,290,607 width=1,116) (actual time=11,517.627..13,690.249 rows=7,472,315 loops=1)

7. 4,606.326 12,821.957 ↓ 1.2 7,472,315 1

Sort (cost=2,823,646.34..2,839,372.85 rows=6,290,607 width=1,116) (actual time=11,517.625..12,821.957 rows=7,472,315 loops=1)

  • Sort Key: (CASE WHEN ((date_part('dow'::text, (access_transactions.tran_date_est)::timestamp without time zone) + '1'::double precision) = '1'::double precision) THEN '7'::double precision ELSE ((date_part('dow'::text, (access_transactions.tran_date_est)::timestamp without time zone) + '1'::double precision) - '1'::double precision) END)
  • Sort Method: external merge Disk: 469256kB
8. 3,557.760 8,215.631 ↓ 1.2 7,472,315 1

Hash Join (cost=311.88..356,338.84 rows=6,290,607 width=1,116) (actual time=4.101..8,215.631 rows=7,472,315 loops=1)

  • Hash Cond: (access_transactions.badge_id = a13.badge_id)
9. 706.512 4,655.151 ↓ 1.0 3,948,159 1

Hash Join (cost=151.08..263,667.69 rows=3,947,238 width=606) (actual time=1.366..4,655.151 rows=3,948,159 loops=1)

  • Hash Cond: (a16.hour_id = a17.hour_id)
10. 692.006 3,948.618 ↓ 1.0 3,948,159 1

Hash Join (cost=122.86..253,223.75 rows=3,947,238 width=538) (actual time=1.337..3,948.618 rows=3,948,159 loops=1)

  • Hash Cond: (access_transactions.est_minute_id = a16.minute_id)
11. 644.684 3,256.089 ↓ 1.0 3,948,159 1

Hash Join (cost=82.46..242,791.36 rows=3,947,238 width=540) (actual time=0.805..3,256.089 rows=3,948,159 loops=1)

  • Hash Cond: (access_transactions.tran_date_est = a15.day_id)
12. 1,312.203 2,610.641 ↓ 1.0 3,948,159 1

Hash Join (cost=13.15..232,340.10 rows=3,947,238 width=536) (actual time=0.030..2,610.641 rows=3,948,159 loops=1)

  • Hash Cond: (access_transactions.access_status_id = a11.access_status_id)
13. 1,298.427 1,298.427 ↓ 1.0 3,948,159 1

Seq Scan on access_transactions (cost=0.00..221,651.78 rows=3,947,238 width=20) (actual time=0.011..1,298.427 rows=3,948,159 loops=1)

  • Filter: ((access_type_id <> 1) AND (tran_type_id <> ALL ('{15,5,-1,0}'::integer[])))
14. 0.003 0.011 ↑ 15.6 9 1

Hash (cost=11.40..11.40 rows=140 width=520) (actual time=0.011..0.011 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.008 0.008 ↑ 15.6 9 1

Seq Scan on lu_access_status a11 (cost=0.00..11.40 rows=140 width=520) (actual time=0.006..0.008 rows=9 loops=1)

16. 0.372 0.764 ↑ 1.0 2,147 1

Hash (cost=42.47..42.47 rows=2,147 width=4) (actual time=0.763..0.764 rows=2,147 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 108kB
17. 0.392 0.392 ↑ 1.0 2,147 1

Seq Scan on day a15 (cost=0.00..42.47 rows=2,147 width=4) (actual time=0.011..0.392 rows=2,147 loops=1)

18. 0.253 0.523 ↑ 1.0 1,440 1

Hash (cost=22.40..22.40 rows=1,440 width=6) (actual time=0.522..0.523 rows=1,440 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
19. 0.270 0.270 ↑ 1.0 1,440 1

Seq Scan on minute a16 (cost=0.00..22.40 rows=1,440 width=6) (actual time=0.019..0.270 rows=1,440 loops=1)

20. 0.006 0.021 ↑ 33.8 24 1

Hash (cost=18.10..18.10 rows=810 width=70) (actual time=0.021..0.021 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.015 0.015 ↑ 33.8 24 1

Seq Scan on hour a17 (cost=0.00..18.10 rows=810 width=70) (actual time=0.011..0.015 rows=24 loops=1)

22. 0.306 2.720 ↑ 1.0 1,500 1

Hash (cost=142.05..142.05 rows=1,500 width=526) (actual time=2.720..2.720 rows=1,500 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
23. 0.396 2.414 ↑ 1.0 1,500 1

Hash Join (cost=110.06..142.05 rows=1,500 width=526) (actual time=1.371..2.414 rows=1,500 loops=1)

  • Hash Cond: (a14.directorate_id = a18.jde_emp_directory_id)
24. 0.479 2.003 ↑ 1.0 1,500 1

Hash Join (cost=96.91..124.84 rows=1,500 width=10) (actual time=1.350..2.003 rows=1,500 loops=1)

  • Hash Cond: (a13.bd_history_id = a14.bd_history_id)
25. 0.194 0.194 ↑ 1.0 1,500 1

Seq Scan on rel_badge_bdhistory a13 (cost=0.00..24.00 rows=1,500 width=16) (actual time=0.010..0.194 rows=1,500 loops=1)

26. 0.551 1.330 ↑ 1.0 3,018 1

Hash (cost=59.18..59.18 rows=3,018 width=10) (actual time=1.330..1.330 rows=3,018 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 174kB
27. 0.779 0.779 ↑ 1.0 3,018 1

Seq Scan on lu_bd_history a14 (cost=0.00..59.18 rows=3,018 width=10) (actual time=0.010..0.779 rows=3,018 loops=1)

28. 0.006 0.015 ↑ 9.3 15 1

Hash (cost=11.40..11.40 rows=140 width=518) (actual time=0.015..0.015 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.009 0.009 ↑ 9.3 15 1

Seq Scan on lu_jde_emp_directory a18 (cost=0.00..11.40 rows=140 width=518) (actual time=0.007..0.009 rows=15 loops=1)

Planning time : 2.609 ms
Execution time : 52,200.961 ms