explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DgLA

Settings
# exclusive inclusive rows x rows loops node
1. 0.074 165.706 ↑ 48.6 17 1

WindowAgg (cost=48,036.44..58,263.73 rows=827 width=293) (actual time=164.182..165.706 rows=17 loops=1)

2. 0.000 164.136 ↑ 48.6 17 1

Finalize GroupAggregate (cost=48,036.44..48,157.79 rows=827 width=125) (actual time=164.009..164.136 rows=17 loops=1)

  • Group Key: st.package_gtin, st.package_pharmacode, st.name_default, st.dosage_id, st.supplier_name, st.package_size, st.production_date, st.planned_production_date
3. 17.327 174.925 ↑ 15.7 44 1

Gather Merge (cost=48,036.44..48,130.16 rows=692 width=125) (actual time=163.991..174.925 rows=44 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.064 157.598 ↑ 23.1 15 3 / 3

Partial GroupAggregate (cost=47,036.42..47,050.26 rows=346 width=125) (actual time=157.562..157.598 rows=15 loops=3)

  • Group Key: st.package_gtin, st.package_pharmacode, st.name_default, st.dosage_id, st.supplier_name, st.package_size, st.production_date, st.planned_production_date
5. 0.311 157.534 ↑ 11.2 31 3 / 3

Sort (cost=47,036.42..47,037.28 rows=346 width=103) (actual time=157.531..157.534 rows=31 loops=3)

  • Sort Key: st.package_gtin, st.package_pharmacode, st.name_default, st.dosage_id, st.supplier_name, st.package_size, st.production_date, st.planned_production_date
  • Sort Method: quicksort Memory: 31kB
6. 134.954 157.223 ↑ 11.2 31 3 / 3

Parallel Bitmap Heap Scan on stock_need st (cost=2,236.93..47,021.83 rows=346 width=103) (actual time=72.404..157.223 rows=31 loops=3)

  • Recheck Cond: ((planned_production_date >= '2020-09-01'::date) AND (planned_production_date < '2020-09-21'::date))
  • Filter: ((lower((name_default)::text) ~~ '%%'::text) AND (lower((supplier_name)::text) ~~ '%%'::text) AND (lower((package_gtin)::text) ~~ '7680380960366'::text))
  • Rows Removed by Filter: 54,407
  • Heap Blocks: exact=6,011
7. 22.269 22.269 ↑ 1.0 163,314 1 / 3

Bitmap Index Scan on idx_stock_need_simple (cost=0.00..2,236.72 rows=166,072 width=0) (actual time=66.806..66.806 rows=163,314 loops=1)

  • Index Cond: ((planned_production_date >= '2020-09-01'::date) AND (planned_production_date < '2020-09-21'::date))
8.          

SubPlan (for WindowAgg)

9. 0.017 0.357 ↑ 1.0 1 17

Aggregate (cost=2.42..2.43 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=17)

10. 0.119 0.340 ↓ 0.0 0 17

Nested Loop (cost=0.00..2.41 rows=1 width=32) (actual time=0.020..0.020 rows=0 loops=17)

  • Join Filter: (sl.location_settings_id = ls.id)
  • Rows Removed by Join Filter: 18
11. 0.068 0.068 ↓ 3.0 3 17

Seq Scan on stock_location sl (cost=0.00..1.24 rows=1 width=64) (actual time=0.003..0.004 rows=3 loops=17)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 16
12. 0.153 0.153 ↓ 6.0 6 51

Seq Scan on stock_location_settings ls (cost=0.00..1.16 rows=1 width=32) (actual time=0.001..0.003 rows=6 loops=51)

  • Filter: ((aggregation_type)::text = 'PACKAGE'::text)
  • Rows Removed by Filter: 7
13. 0.034 0.255 ↑ 1.0 1 17

Aggregate (cost=2.42..2.43 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=17)

14. 0.068 0.221 ↓ 3.0 3 17

Nested Loop (cost=0.00..2.41 rows=1 width=32) (actual time=0.007..0.013 rows=3 loops=17)

  • Join Filter: (sl_1.location_settings_id = ls_1.id)
15. 0.051 0.051 ↓ 3.0 3 17

Seq Scan on stock_location sl_1 (cost=0.00..1.24 rows=1 width=64) (actual time=0.002..0.003 rows=3 loops=17)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 16
16. 0.102 0.102 ↑ 1.0 1 51

Seq Scan on stock_location_settings ls_1 (cost=0.00..1.16 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=51)

  • Filter: ((aggregation_type)::text = 'DOSE'::text)
  • Rows Removed by Filter: 7
17. 0.000 0.272 ↑ 1.0 1 17

Aggregate (cost=2.42..2.43 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=17)

18. 0.068 0.272 ↓ 0.0 0 17

Nested Loop (cost=0.00..2.41 rows=1 width=32) (actual time=0.016..0.016 rows=0 loops=17)

  • Join Filter: (sl_2.location_settings_id = ls_2.id)
  • Rows Removed by Join Filter: 3
19. 0.051 0.051 ↓ 3.0 3 17

Seq Scan on stock_location sl_2 (cost=0.00..1.24 rows=1 width=64) (actual time=0.002..0.003 rows=3 loops=17)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 16
20. 0.153 0.153 ↑ 1.0 1 51

Seq Scan on stock_location_settings ls_2 (cost=0.00..1.16 rows=1 width=32) (actual time=0.002..0.003 rows=1 loops=51)

  • Filter: ((aggregation_type)::text = 'CANISTER'::text)
  • Rows Removed by Filter: 12
21. 0.000 0.272 ↑ 1.0 1 17

Aggregate (cost=2.42..2.43 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=17)

22. 0.068 0.272 ↓ 0.0 0 17

Nested Loop (cost=0.00..2.41 rows=1 width=32) (actual time=0.016..0.016 rows=0 loops=17)

  • Join Filter: (sl_3.location_settings_id = ls_3.id)
  • Rows Removed by Join Filter: 6
23. 0.051 0.051 ↓ 3.0 3 17

Seq Scan on stock_location sl_3 (cost=0.00..1.24 rows=1 width=64) (actual time=0.002..0.003 rows=3 loops=17)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 16
24. 0.153 0.153 ↓ 2.0 2 51

Seq Scan on stock_location_settings ls_3 (cost=0.00..1.16 rows=1 width=32) (actual time=0.002..0.003 rows=2 loops=51)

  • Filter: ((aggregation_type)::text = 'PRODUCTION'::text)
  • Rows Removed by Filter: 11
25. 0.034 0.340 ↑ 1.0 1 17

Aggregate (cost=2.49..2.50 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=17)

26. 0.153 0.306 ↓ 3.0 3 17

Nested Loop (cost=0.00..2.48 rows=1 width=32) (actual time=0.008..0.018 rows=3 loops=17)

  • Join Filter: (sl_4.location_settings_id = ls_4.id)
  • Rows Removed by Join Filter: 21
27. 0.051 0.051 ↓ 3.0 3 17

Seq Scan on stock_location sl_4 (cost=0.00..1.24 rows=1 width=64) (actual time=0.002..0.003 rows=3 loops=17)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 16
28. 0.102 0.102 ↓ 2.0 8 51

Seq Scan on stock_location_settings ls_4 (cost=0.00..1.20 rows=4 width=32) (actual time=0.001..0.002 rows=8 loops=51)

  • Filter: ((aggregation_type)::text = ANY ('{PACKAGE,DOSE,CANISTER,PRODUCTION}'::text[]))
Planning time : 1.216 ms