explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jBti : Optimization for: plan #DR4m

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4,208.659 19,320.122 ↓ 7.1 85,367 1

Merge Join (cost=3,464,259.1..3,668,429.17 rows=12,104 width=1,843) (actual time=15,005.72..19,320.122 rows=85,367 loops=1)

  • Buffers: shared hit=1,549,106 read=651,184 dirtied=3
2. 86.477 8,916.120 ↓ 7.1 85,367 1

Sort (cost=1,763,025.57..1,763,055.83 rows=12,104 width=783) (actual time=8,895.621..8,916.12 rows=85,367 loops=1)

  • Sort Key: b.id
  • Sort Method: quicksort Memory: 26,879kB
  • Buffers: shared hit=612,413 read=213,574 dirtied=3
3. 48.216 8,829.643 ↓ 7.1 85,367 1

Hash Join (cost=1,743,953.05..1,762,204.73 rows=12,104 width=783) (actual time=8,180.399..8,829.643 rows=85,367 loops=1)

  • Buffers: shared hit=612,413 read=213,574 dirtied=3
4. 27.338 959.042 ↓ 7.1 85,367 1

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

  • Buffers: shared hit=362,284 read=4,636
5. 30.280 930.536 ↓ 7.1 85,367 1

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

  • Buffers: shared hit=360,934 read=4,636
6. 32.490 899.627 ↓ 7.1 85,367 1

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

  • Buffers: shared hit=360,921 read=4,635
7. 32.178 867.122 ↓ 7.1 85,367 1

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

  • Buffers: shared hit=360,920 read=4,635
8. 98.903 834.910 ↓ 7.1 85,367 1

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

  • Buffers: shared hit=360,919 read=4,635
9. 159.644 565.273 ↓ 7.1 85,367 1

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

  • Buffers: shared hit=19,440 read=4,635
10. 50.348 50.348 ↓ 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.025..50.348 rows=403,774 loops=1)

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

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

  • Buffers: shared hit=19,440
12. 32.252 322.749 ↓ 7.1 85,367 1

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

  • Buffers: shared hit=19,440
13. 186.491 289.923 ↑ 2.2 85,367 1

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

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

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

  • Buffers: shared hit=17,746
15. 0.295 2.632 ↑ 1.0 1,452 1

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

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

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

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

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

  • Buffers: shared hit=618
18. 0.134 0.543 ↓ 1.1 97 1

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

  • Buffers: shared hit=618
19. 0.215 0.215 ↑ 1.0 97 1

Index Scan using subarea_pkey on subarea cs (cost=0.28..60.8 rows=97 width=19) (actual time=0.056..0.215 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. 0.097 0.194 ↑ 1.0 1 97

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

  • Filter: acc.active
  • Heap Blocks: exact=97
  • Buffers: shared hit=388
21. 0.097 0.097 ↑ 1.0 1 97

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

  • Index Cond: (acc.id = cs.accountable_id)
  • Buffers: shared hit=291
22. 170.734 170.734 ↑ 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.002..0.002 rows=1 loops=85,367)

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

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

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

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

  • Buffers: shared hit=1
25. 0.006 0.015 ↓ 1.1 24 1

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

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

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

  • Buffers: shared hit=1
27. 0.229 0.629 ↑ 1.0 1,336 1

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

  • Buffers: shared hit=13 read=1
28. 0.400 0.400 ↑ 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.4 rows=1,336 loops=1)

  • Buffers: shared hit=13 read=1
29. 0.095 1.168 ↑ 1.0 511 1

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

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

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

  • Filter: acc_lastevent.active
  • Buffers: shared hit=1,350
31. 12.180 7,822.385 ↓ 327.1 65,425 1

Hash (cost=1,710,937.53..1,710,937.53 rows=200 width=12) (actual time=7,822.385..7,822.385 rows=65,425 loops=1)

  • Buffers: shared hit=250,129 read=208,938 dirtied=3
32. 9.210 7,810.205 ↓ 327.1 65,425 1

Subquery Scan on event_collect (cost=1,710,933.53..1,710,937.53 rows=200 width=12) (actual time=7,784.932..7,810.205 rows=65,425 loops=1)

  • Buffers: shared hit=250,129 read=208,938 dirtied=3
33. 1,514.310 7,800.995 ↓ 327.1 65,425 1

HashAggregate (cost=1,710,933.53..1,710,935.53 rows=200 width=12) (actual time=7,784.931..7,800.995 rows=65,425 loops=1)

  • Group Key: bh.batchunity_id
  • Buffers: shared hit=250,129 read=208,938 dirtied=3
34. 490.416 6,286.685 ↓ 2.5 4,725,314 1

Append (cost=0..1,701,424.2 rows=1,901,866 width=12) (actual time=2.124..6,286.685 rows=4,725,314 loops=1)

  • Buffers: shared hit=250,129 read=208,938 dirtied=3
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. 236.551 236.551 ↓ 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.121..236.551 rows=209,041 loops=1)

  • Filter: ((bh_1.eventtype_id = ANY ('{2,4}'::integer[])) AND (bh_1.subarea_id = 2,706))
  • Buffers: shared hit=4,218 read=11,224
37. 1,026.880 1,302.356 ↓ 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=307.077..1,302.356 rows=1,751,925 loops=1)

  • Filter: (bh_2.subarea_id = 2,706)
  • Heap Blocks: exact=84,688
  • Buffers: shared hit=103,607 read=58
38. 275.476 275.476 ↓ 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=275.476..275.476 rows=6,942,989 loops=1)

  • Index Cond: (bh_2.eventtype_id = ANY ('{2,4}'::integer[]))
  • Buffers: shared hit=18,977
39. 2,461.706 3,059.511 ↓ 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=651.487..3,059.511 rows=1,830,486 loops=1)

  • Filter: (bh_3.subarea_id = 2,706)
  • Heap Blocks: exact=217,453
  • Buffers: shared hit=139,095 read=118,189
40. 597.805 597.805 ↑ 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=597.805..597.805 rows=14,574,857 loops=1)

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

Bitmap Heap Scan on batchunityhistory_part_2020 bh_4 (cost=77,223.62..317,343.7 rows=282,855 width=12) (actual time=209.501..1,197.849 rows=933,862 loops=1)

  • Filter: (bh_4.subarea_id = 2,706)
  • Heap Blocks: exact=69,254
  • Buffers: shared hit=3,209 read=79,467 dirtied=3
42. 196.600 196.600 ↓ 1.0 4,126,920 1

Bitmap Index Scan on history_part_2020_eventtype_id_idx (cost=0..77,152.91 rows=4,061,872 width=0) (actual time=196.6..196.6 rows=4,126,920 loops=1)

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

GroupAggregate (cost=1,701,233.53..1,701,267.11 rows=200 width=12) (actual time=6,109.976..6,109.976 rows=0 loops=1)

  • Group Key: bh_5.batchunity_id
  • Buffers: shared hit=21,459 read=437,608
44. 0.011 6,109.975 ↓ 0.0 0 1

Sort (cost=1,701,233.53..1,701,244.05 rows=4,211 width=12) (actual time=6,109.975..6,109.975 rows=0 loops=1)

  • Sort Key: bh_5.batchunity_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=21,459 read=437,608
45. 0.005 6,109.964 ↓ 0.0 0 1

Append (cost=0..1,700,980.03 rows=4,211 width=12) (actual time=6,109.964..6,109.964 rows=0 loops=1)

  • Buffers: shared hit=21,459 read=437,608
46. 0.006 0.006 ↓ 0.0 0 1

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

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

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

  • Filter: ((bh_6.eventtype_id = ANY ('{2,4}'::integer[])) AND (bh_6.subarea_id = 4,654))
  • Buffers: shared hit=11,224 read=4,218
48. 1,097.657 1,416.420 ↓ 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,416.42..1,416.42 rows=0 loops=1)

  • Filter: (bh_7.subarea_id = 4,654)
  • Heap Blocks: exact=84,688
  • Buffers: shared hit=177 read=103,488
49. 318.763 318.763 ↓ 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=318.762..318.763 rows=6,942,989 loops=1)

  • Index Cond: (bh_7.eventtype_id = ANY ('{2,4}'::integer[]))
  • Buffers: shared hit=2 read=18,975
50. 2,597.861 3,349.332 ↓ 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=3,349.332..3,349.332 rows=0 loops=1)

  • Filter: (bh_8.subarea_id = 4,654)
  • Heap Blocks: exact=217,453
  • Buffers: shared hit=5 read=257,279
51. 751.471 751.471 ↑ 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.47..751.471 rows=14,574,857 loops=1)

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

Bitmap Heap Scan on batchunityhistory_part_2020 bh_9 (cost=77,153.13..317,273.21 rows=873 width=12) (actual time=1,146.339..1,146.339 rows=0 loops=1)

  • Filter: (bh_9.subarea_id = 4,654)
  • Heap Blocks: exact=69,254
  • Buffers: shared hit=10,053 read=72,623
53. 213.398 213.398 ↓ 1.0 4,126,920 1

Bitmap Index Scan on history_part_2020_eventtype_id_idx (cost=0..77,152.91 rows=4,061,872 width=0) (actual time=213.398..213.398 rows=4,126,920 loops=1)

  • Index Cond: (bh_9.eventtype_id = ANY ('{2,4}'::integer[]))
  • Buffers: shared hit=5 read=13,417
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,885 read=2
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 : 6.534 ms
Execution time : 19,332.157 ms