explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ENDO : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #DR4m; plan #jBti; plan #7py4; plan #l33D; plan #ANjs; plan #oA93; plan #Ec5oZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,132.304 22,447.628 ↓ 7.1 85,367 1

Merge Join (cost=3,464,361.9..3,668,531.98 rows=12,104 width=1,843) (actual time=18,191.676..22,447.628 rows=85,367 loops=1)

  • Buffers: shared hit=1,417,339 read=783,412, temp read=4,929 written=4,928
2. 157.447 11,070.405 ↓ 7.1 85,367 1

Sort (cost=1,763,077.05..1,763,107.31 rows=12,104 width=783) (actual time=11,031.98..11,070.405 rows=85,367 loops=1)

  • Sort Key: b.id
  • Sort Method: external merge Disk: 17,288kB
  • Buffers: shared hit=436,519 read=389,917, temp read=4,929 written=4,928
3. 54.321 10,912.958 ↓ 7.1 85,367 1

Hash Join (cost=1,744,004.53..1,762,256.2 rows=12,104 width=783) (actual time=10,107.588..10,912.958 rows=85,367 loops=1)

  • Buffers: shared hit=436,519 read=389,917, temp read=2,768 written=2,766
4. 31.025 1,163.481 ↓ 7.1 85,367 1

Hash Join (cost=33,013.02..51,232.93 rows=12,104 width=775) (actual time=412.425..1,163.481 rows=85,367 loops=1)

  • Buffers: shared hit=362,722 read=4,635, temp read=2,768 written=2,766
5. 34.243 1,131.353 ↓ 7.1 85,367 1

Hash Join (cost=31,651.07..49,838.95 rows=12,104 width=755) (actual time=411.317..1,131.353 rows=85,367 loops=1)

  • Buffers: shared hit=361,372 read=4,635, temp read=2,768 written=2,766
6. 37.078 1,096.594 ↓ 7.1 85,367 1

Hash Join (cost=31,606.15..49,762.16 rows=12,104 width=744) (actual time=410.796..1,096.594 rows=85,367 loops=1)

  • Buffers: shared hit=361,358 read=4,635, temp read=2,768 written=2,766
7. 36.372 1,059.495 ↓ 7.1 85,367 1

Hash Join (cost=31,604.66..49,722.78 rows=12,104 width=232) (actual time=410.768..1,059.495 rows=85,367 loops=1)

  • Buffers: shared hit=361,357 read=4,635, temp read=2,768 written=2,766
8. 68.237 1,023.083 ↓ 7.1 85,367 1

Nested Loop (cost=31,601.74..49,687.67 rows=12,104 width=221) (actual time=410.717..1,023.083 rows=85,367 loops=1)

  • Buffers: shared hit=361,356 read=4,635, temp read=2,768 written=2,766
9. 231.706 698.745 ↓ 7.1 85,367 1

Hash Join (cost=31,601.32..43,300.04 rows=12,104 width=171) (actual time=410.698..698.745 rows=85,367 loops=1)

  • Buffers: shared hit=19,877 read=4,635, temp read=2,768 written=2,766
10. 57.652 57.652 ↓ 1.0 403,774 1

Seq Scan on checkout_batch_unity cbu (cost=0..8,671.37 rows=403,637 width=38) (actual time=0.039..57.652 rows=403,774 loops=1)

  • Buffers: shared read=4,635
11. 44.398 409.387 ↓ 7.1 85,367 1

Hash (cost=31,419.76..31,419.76 rows=12,104 width=145) (actual time=409.387..409.387 rows=85,367 loops=1)

  • Buffers: shared hit=19,877, temp written=730
12. 36.818 364.989 ↓ 7.1 85,367 1

Hash Join (cost=1,920.79..31,419.76 rows=12,104 width=145) (actual time=5.837..364.989 rows=85,367 loops=1)

  • Buffers: shared hit=19,877
13. 208.987 326.192 ↑ 2.2 85,367 1

Hash Join (cost=1,178.23..29,855.3 rows=186,889 width=110) (actual time=3.853..326.192 rows=85,367 loops=1)

  • Buffers: shared hit=18,822
14. 113.375 113.375 ↓ 1.0 865,855 1

Seq Scan on batchunity b (cost=0..26,403.14 rows=865,714 width=83) (actual time=0.006..113.375 rows=865,855 loops=1)

  • Buffers: shared hit=17,746
15. 0.447 3.830 ↑ 1.0 1,452 1

Hash (cost=1,160.08..1,160.08 rows=1,452 width=35) (actual time=3.83..3.83 rows=1,452 loops=1)

  • Buffers: shared hit=1,076
16. 3.383 3.383 ↑ 1.0 1,452 1

Seq Scan on batch bt (cost=0..1,160.08 rows=1,452 width=35) (actual time=0.092..3.383 rows=1,452 loops=1)

  • Filter: (bt.company_id = 501)
  • Buffers: shared hit=1,076
17. 0.047 1.979 ↓ 1.1 97 1

Hash (cost=741.45..741.45 rows=89 width=43) (actual time=1.979..1.979 rows=97 loops=1)

  • Buffers: shared hit=1,055
18. 0.100 1.932 ↓ 1.1 97 1

Nested Loop (cost=3.28..741.45 rows=89 width=43) (actual time=0.076..1.932 rows=97 loops=1)

  • Buffers: shared hit=1,055
19. 0.280 0.280 ↑ 1.0 97 1

Index Scan using subarea_pkey on subarea cs (cost=0.28..60.8 rows=97 width=19) (actual time=0.031..0.28 rows=97 loops=1)

  • Index Cond: (cs.id = ANY ('{1051,1201,801,851,800,2817,4110,4111,4112,11404,2805,11408,10538,14255,2802,2812,2706,6812,7380,7381,13154,10457,2853,2810,2813,2811,2814,2854,2705,4108,6250,10675,7385,2803,11796,11407,2820,4654,8503,9552,2818,13057,11405,11406,8569,2819,751,10674,13102,2816,2815,2807,2804,2806,10673,12802,12803,12804,12805,12815,12806,12807,12808,12809,12810,12811,12812,12816,12813,12814,1101,1301,901,1251,1151,1001,951,10352,10353,9804,2823,2808,2809,11797,2824,2822,4657,4109,3503,2825,1756,2821,10402,10403,11452,2752,7402}'::integer[]))
  • Buffers: shared hit=230
20. 1.261 1.552 ↑ 1.0 1 97

Bitmap Heap Scan on accountable acc (cost=3..7.02 rows=1 width=28) (actual time=0.016..0.016 rows=1 loops=97)

  • Filter: acc.active
  • Heap Blocks: exact=534
  • Buffers: shared hit=825
21. 0.291 0.291 ↓ 6.0 6 97

Bitmap Index Scan on accountable_pkey (cost=0..3 rows=1 width=0) (actual time=0.003..0.003 rows=6 loops=97)

  • Index Cond: (acc.id = cs.accountable_id)
  • Buffers: shared hit=291
22. 256.101 256.101 ↑ 1.0 1 85,367

Index Scan using batchunity_detail_pkey on batchunity_detail bd (cost=0.42..0.53 rows=1 width=54) (actual time=0.003..0.003 rows=1 loops=85,367)

  • Index Cond: (bd.batchunity_id = b.id)
  • Buffers: shared hit=341,479
23. 0.021 0.040 ↓ 1.2 100 1

Hash (cost=1.85..1.85 rows=85 width=19) (actual time=0.04..0.04 rows=100 loops=1)

  • Buffers: shared hit=1
24. 0.019 0.019 ↓ 1.2 100 1

Seq Scan on batchunitystatus bus (cost=0..1.85 rows=85 width=19) (actual time=0.006..0.019 rows=100 loops=1)

  • Buffers: shared hit=1
25. 0.009 0.021 ↓ 1.1 24 1

Hash (cost=1.22..1.22 rows=22 width=520) (actual time=0.021..0.021 rows=24 loops=1)

  • Buffers: shared hit=1
26. 0.012 0.012 ↓ 1.1 24 1

Seq Scan on batchunityeventtype buevt (cost=0..1.22 rows=22 width=520) (actual time=0.006..0.012 rows=24 loops=1)

  • Buffers: shared hit=1
27. 0.285 0.516 ↑ 1.0 1,336 1

Hash (cost=27.74..27.74 rows=1,374 width=19) (actual time=0.516..0.516 rows=1,336 loops=1)

  • Buffers: shared hit=14
28. 0.231 0.231 ↑ 1.0 1,336 1

Seq Scan on subarea cs_lastevent (cost=0..27.74 rows=1,374 width=19) (actual time=0.005..0.231 rows=1,336 loops=1)

  • Buffers: shared hit=14
29. 0.105 1.103 ↑ 1.0 511 1

Hash (cost=1,355.57..1,355.57 rows=511 width=28) (actual time=1.103..1.103 rows=511 loops=1)

  • Buffers: shared hit=1,350
30. 0.998 0.998 ↑ 1.0 511 1

Seq Scan on accountable acc_lastevent (cost=0..1,355.57 rows=511 width=28) (actual time=0.01..0.998 rows=511 loops=1)

  • Filter: acc_lastevent.active
  • Buffers: shared hit=1,350
31. 14.170 9,695.156 ↓ 327.1 65,425 1

Hash (cost=1,710,989..1,710,989 rows=200 width=12) (actual time=9,695.156..9,695.156 rows=65,425 loops=1)

  • Buffers: shared hit=73,797 read=385,282
32. 10.753 9,680.986 ↓ 327.1 65,425 1

Subquery Scan on event_collect (cost=1,710,985..1,710,989 rows=200 width=12) (actual time=9,651.77..9,680.986 rows=65,425 loops=1)

  • Buffers: shared hit=73,797 read=385,282
33. 1,776.181 9,670.233 ↓ 327.1 65,425 1

HashAggregate (cost=1,710,985..1,710,987 rows=200 width=12) (actual time=9,651.769..9,670.233 rows=65,425 loops=1)

  • Group Key: bh.batchunity_id
  • Buffers: shared hit=73,797 read=385,282
34. 536.522 7,894.052 ↓ 2.5 4,725,314 1

Append (cost=0..1,701,475.47 rows=1,901,907 width=12) (actual time=2.444..7,894.052 rows=4,725,314 loops=1)

  • Buffers: shared hit=73,797 read=385,282
35. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on batchunityhistory_part bh (cost=0..0 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((bh.eventtype_id = ANY ('{2,4}'::integer[])) AND (bh.subarea_id = 2,706))
36. 255.464 255.464 ↓ 1.7 209,041 1

Seq Scan on batchunityhistory_part_2017 bh_1 (cost=0..35,657.26 rows=120,979 width=12) (actual time=2.441..255.464 rows=209,041 loops=1)

  • Filter: ((bh_1.eventtype_id = ANY ('{2,4}'::integer[])) AND (bh_1.subarea_id = 2,706))
  • Buffers: shared hit=2,863 read=12,579
37. 1,300.287 1,642.060 ↓ 2.3 1,751,925 1

Bitmap Heap Scan on batchunityhistory_part_2018 bh_2 (cost=114,998.25..410,429.74 rows=746,551 width=12) (actual time=377.293..1,642.06 rows=1,751,925 loops=1)

  • Filter: (bh_2.subarea_id = 2,706)
  • Heap Blocks: exact=84,688
  • Buffers: shared hit=17,555 read=86,110
38. 341.773 341.773 ↓ 1.0 6,942,989 1

Bitmap Index Scan on history_part_2018_eventtype_id_idx (cost=0..114,811.61 rows=6,934,766 width=0) (actual time=341.773..341.773 rows=6,942,989 loops=1)

  • Index Cond: (bh_2.eventtype_id = ANY ('{2,4}'::integer[]))
  • Buffers: shared hit=2 read=18,975
39. 3,532.059 4,278.926 ↓ 2.4 1,830,486 1

Bitmap Heap Scan on batchunityhistory_part_2019 bh_3 (cost=242,028.83..937,993.49 rows=751,480 width=12) (actual time=783.071..4,278.926 rows=1,830,486 loops=1)

  • Filter: (bh_3.subarea_id = 2,706)
  • Heap Blocks: exact=133,722 lossy=83,731
  • Buffers: shared hit=4 read=257,280
40. 746.867 746.867 ↑ 1.0 14,574,857 1

Bitmap Index Scan on history_part_2019_eventtype_id_idx (cost=0..241,840.96 rows=14,578,644 width=0) (actual time=746.867..746.867 rows=14,574,857 loops=1)

  • Index Cond: (bh_3.eventtype_id = ANY ('{2,4}'::integer[]))
  • Buffers: shared hit=4 read=39,827
41. 769.787 1,181.078 ↓ 3.3 933,862 1

Bitmap Heap Scan on batchunityhistory_part_2020 bh_4 (cost=77,240.06..317,394.97 rows=282,896 width=12) (actual time=430.933..1,181.078 rows=933,862 loops=1)

  • Filter: (bh_4.subarea_id = 2,706)
  • Heap Blocks: exact=69,263
  • Buffers: shared hit=53,375 read=29,313
42. 411.291 411.291 ↓ 1.0 4,127,518 1

Bitmap Index Scan on history_part_2020_eventtype_id_idx (cost=0..77,169.33 rows=4,062,461 width=0) (actual time=411.291..411.291 rows=4,127,518 loops=1)

  • Index Cond: (bh_4.eventtype_id = ANY ('{2,4}'::integer[]))
  • Buffers: shared hit=7 read=13,418
43. 0.002 7,159.552 ↓ 0.0 0 1

GroupAggregate (cost=1,701,284.86..1,701,318.45 rows=200 width=12) (actual time=7,159.552..7,159.552 rows=0 loops=1)

  • Group Key: bh_5.batchunity_id
  • Buffers: shared hit=65,623 read=393,456
44. 0.011 7,159.550 ↓ 0.0 0 1

Sort (cost=1,701,284.86..1,701,295.39 rows=4,212 width=12) (actual time=7,159.55..7,159.55 rows=0 loops=1)

  • Sort Key: bh_5.batchunity_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=65,623 read=393,456
45. 0.005 7,159.539 ↓ 0.0 0 1

Append (cost=0..1,701,031.29 rows=4,212 width=12) (actual time=7,159.539..7,159.539 rows=0 loops=1)

  • Buffers: shared hit=65,623 read=393,456
46. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on batchunityhistory_part bh_5 (cost=0..0 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: ((bh_5.eventtype_id = ANY ('{2,4}'::integer[])) AND (bh_5.subarea_id = 4,654))
47. 233.529 233.529 ↓ 0.0 0 1

Seq Scan on batchunityhistory_part_2017 bh_6 (cost=0..35,657.26 rows=5 width=12) (actual time=233.529..233.529 rows=0 loops=1)

  • Filter: ((bh_6.eventtype_id = ANY ('{2,4}'::integer[])) AND (bh_6.subarea_id = 4,654))
  • Buffers: shared hit=2,863 read=12,579
48. 1,127.675 1,465.777 ↓ 0.0 0 1

Bitmap Heap Scan on batchunityhistory_part_2018 bh_7 (cost=114,811.85..410,243.34 rows=933 width=12) (actual time=1,465.777..1,465.777 rows=0 loops=1)

  • Filter: (bh_7.subarea_id = 4,654)
  • Heap Blocks: exact=84,688
  • Buffers: shared hit=12,258 read=91,407
49. 338.102 338.102 ↓ 1.0 6,942,989 1

Bitmap Index Scan on history_part_2018_eventtype_id_idx (cost=0..114,811.61 rows=6,934,766 width=0) (actual time=338.102..338.102 rows=6,942,989 loops=1)

  • Index Cond: (bh_7.eventtype_id = ANY ('{2,4}'::integer[]))
  • Buffers: shared hit=2 read=18,975
50. 3,697.219 4,448.246 ↓ 0.0 0 1

Bitmap Heap Scan on batchunityhistory_part_2019 bh_8 (cost=241,841.56..937,806.22 rows=2,399 width=12) (actual time=4,448.246..4,448.246 rows=0 loops=1)

  • Filter: (bh_8.subarea_id = 4,654)
  • Heap Blocks: exact=133,722 lossy=83,731
  • Buffers: shared hit=5 read=257,279
51. 751.027 751.027 ↑ 1.0 14,574,857 1

Bitmap Index Scan on history_part_2019_eventtype_id_idx (cost=0..241,840.96 rows=14,578,644 width=0) (actual time=751.027..751.027 rows=14,574,857 loops=1)

  • Index Cond: (bh_8.eventtype_id = ANY ('{2,4}'::integer[]))
  • Buffers: shared hit=4 read=39,827
52. 787.542 1,011.978 ↓ 0.0 0 1

Bitmap Heap Scan on batchunityhistory_part_2020 bh_9 (cost=77,169.55..317,324.47 rows=874 width=12) (actual time=1,011.977..1,011.978 rows=0 loops=1)

  • Filter: (bh_9.subarea_id = 4,654)
  • Heap Blocks: exact=69,263
  • Buffers: shared hit=50,497 read=32,191
53. 224.436 224.436 ↓ 1.0 4,127,518 1

Bitmap Index Scan on history_part_2020_eventtype_id_idx (cost=0..77,169.33 rows=4,062,461 width=0) (actual time=224.436..224.436 rows=4,127,518 loops=1)

  • Index Cond: (bh_9.eventtype_id = ANY ('{2,4}'::integer[]))
  • Buffers: shared hit=7 read=13,418
54.          

SubPlan (for Merge Join)

55. 85.367 85.367 ↓ 0.0 0 85,367

Index Scan using employee_pk on employee e (cost=0.29..8.3 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=85,367)

  • Index Cond: (e.id = bd.possession_employee_id)
  • Buffers: shared hit=69,857 read=30
56. 0.000 0.000 ↓ 0.0 0 85,367

Index Scan using employee_pk on employee e_1 (cost=0.29..8.3 rows=1 width=27) (actual time=0..0 rows=0 loops=85,367)

  • Index Cond: (e_1.id = bd.dedicated_employee_id)
Planning time : 10.928 ms
Execution time : 22,463.105 ms