explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TIVZ : Optimization for: Optimization for: plan #Kpqk; plan #ePxW

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3,058.716 45,716.517 ↑ 705.0 35,692 1

Unique (cost=14,495,259.82..15,187,226.59 rows=25,162,428 width=1,224) (actual time=41,465.701..45,716.517 rows=35,692 loops=1)

2. 35,900.545 42,657.801 ↑ 3.4 7,472,315 1

Sort (cost=14,495,259.82..14,558,165.89 rows=25,162,428 width=1,224) (actual time=41,465.697..42,657.801 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: quicksort Memory: 1247403kB
3. 1,171.623 6,757.256 ↑ 3.4 7,472,315 1

Gather (cost=518,136.25..3,735,519.57 rows=25,162,428 width=1,224) (actual time=4,503.464..6,757.256 rows=7,472,315 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 858.817 5,585.633 ↑ 4.2 2,490,772 3 / 3

Merge Join (cost=517,136.25..1,218,276.77 rows=10,484,345 width=1,224) (actual time=4,479.613..5,585.633 rows=2,490,772 loops=3)

  • Merge Cond: ((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_id)::double precision))
5. 1,051.483 4,626.747 ↑ 1.1 2,490,772 3 / 3

Sort (cost=517,079.67..523,632.38 rows=2,621,086 width=1,116) (actual time=4,479.501..4,626.747 rows=2,490,772 loops=3)

  • 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: quicksort Memory: 401764kB
  • Worker 0: Sort Method: quicksort Memory: 389120kB
  • Worker 1: Sort Method: quicksort Memory: 398418kB
6. 1,420.014 3,575.264 ↑ 1.1 2,490,772 3 / 3

Hash Join (cost=311.88..237,649.19 rows=2,621,086 width=1,116) (actual time=4.508..3,575.264 rows=2,490,772 loops=3)

  • Hash Cond: (access_transactions.badge_id = a13.badge_id)
7. 282.715 2,152.270 ↑ 1.2 1,316,053 3 / 3

Hash Join (cost=151.08..198,942.41 rows=1,644,682 width=606) (actual time=1.397..2,152.270 rows=1,316,053 loops=3)

  • Hash Cond: (a16.hour_id = a17.hour_id)
8. 299.649 1,869.513 ↑ 1.2 1,316,053 3 / 3

Hash Join (cost=122.86..194,574.31 rows=1,644,682 width=538) (actual time=1.330..1,869.513 rows=1,316,053 loops=3)

  • Hash Cond: (access_transactions.est_minute_id = a16.minute_id)
9. 261.964 1,569.459 ↑ 1.2 1,316,053 3 / 3

Hash Join (cost=82.46..190,203.92 rows=1,644,682 width=540) (actual time=0.878..1,569.459 rows=1,316,053 loops=3)

  • Hash Cond: (access_transactions.tran_date_est = a15.day_id)
10. 526.571 1,306.764 ↑ 1.2 1,316,053 3 / 3

Hash Join (cost=13.15..185,808.80 rows=1,644,682 width=536) (actual time=0.076..1,306.764 rows=1,316,053 loops=3)

  • Hash Cond: (access_transactions.access_status_id = a11.access_status_id)
11. 780.159 780.159 ↑ 1.2 1,316,053 3 / 3

Parallel Seq Scan on access_transactions (cost=0.00..181,347.66 rows=1,644,682 width=20) (actual time=0.019..780.159 rows=1,316,053 loops=3)

  • Filter: ((access_type_id <> 1) AND (tran_type_id <> ALL ('{15,5,-1,0}'::integer[])))
12. 0.009 0.034 ↑ 15.6 9 3 / 3

Hash (cost=11.40..11.40 rows=140 width=520) (actual time=0.034..0.034 rows=9 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.025 0.025 ↑ 15.6 9 3 / 3

Seq Scan on lu_access_status a11 (cost=0.00..11.40 rows=140 width=520) (actual time=0.024..0.025 rows=9 loops=3)

14. 0.322 0.731 ↑ 1.0 2,147 3 / 3

Hash (cost=42.47..42.47 rows=2,147 width=4) (actual time=0.731..0.731 rows=2,147 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 108kB
15. 0.409 0.409 ↑ 1.0 2,147 3 / 3

Seq Scan on day a15 (cost=0.00..42.47 rows=2,147 width=4) (actual time=0.020..0.409 rows=2,147 loops=3)

16. 0.182 0.405 ↑ 1.0 1,440 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
17. 0.223 0.223 ↑ 1.0 1,440 3 / 3

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

18. 0.012 0.042 ↑ 33.8 24 3 / 3

Hash (cost=18.10..18.10 rows=810 width=70) (actual time=0.042..0.042 rows=24 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.030 0.030 ↑ 33.8 24 3 / 3

Seq Scan on hour a17 (cost=0.00..18.10 rows=810 width=70) (actual time=0.028..0.030 rows=24 loops=3)

20. 0.329 2.980 ↑ 1.0 1,500 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
21. 0.323 2.651 ↑ 1.0 1,500 3 / 3

Hash Join (cost=110.06..142.05 rows=1,500 width=526) (actual time=1.807..2.651 rows=1,500 loops=3)

  • Hash Cond: (a14.directorate_id = a18.jde_emp_directory_id)
22. 0.422 2.280 ↑ 1.0 1,500 3 / 3

Hash Join (cost=96.91..124.84 rows=1,500 width=10) (actual time=1.715..2.280 rows=1,500 loops=3)

  • Hash Cond: (a13.bd_history_id = a14.bd_history_id)
23. 0.216 0.216 ↑ 1.0 1,500 3 / 3

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

24. 0.564 1.642 ↑ 1.0 3,018 3 / 3

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

  • Buckets: 4096 Batches: 1 Memory Usage: 174kB
25. 1.078 1.078 ↑ 1.0 3,018 3 / 3

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

26. 0.011 0.048 ↑ 9.3 15 3 / 3

Hash (cost=11.40..11.40 rows=140 width=518) (actual time=0.048..0.048 rows=15 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.037 0.037 ↑ 9.3 15 3 / 3

Seq Scan on lu_jde_emp_directory a18 (cost=0.00..11.40 rows=140 width=518) (actual time=0.034..0.037 rows=15 loops=3)

28. 99.995 100.069 ↓ 2,900.0 2,320,001 3 / 3

Sort (cost=56.58..58.58 rows=800 width=70) (actual time=0.090..100.069 rows=2,320,001 loops=3)

  • Sort Key: ((a19.day_of_week_id)::double precision)
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
29. 0.074 0.074 ↑ 114.3 7 3 / 3

Seq Scan on day_of_week a19 (cost=0.00..18.00 rows=800 width=70) (actual time=0.072..0.074 rows=7 loops=3)

Planning time : 5.157 ms