explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o4j3

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 12,491.741 ↓ 8.5 17 1

Limit (cost=109,313.23..1,371,871.15 rows=2 width=293) (actual time=3,096.647..12,491.741 rows=17 loops=1)

2. 58.598 12,491.736 ↓ 8.5 17 1

Subquery Scan on stockcover0_ (cost=109,313.23..1,371,871.15 rows=2 width=293) (actual time=3,096.646..12,491.736 rows=17 loops=1)

  • Filter: ((stockcover0_.planned_production_date >= '2020-09-01'::date) AND (stockcover0_.planned_production_date < '2020-09-21'::date) AND (lower((stockcover0_.name_default)::text) ~~ '%%'::text) AND (lower((stockcover0_.supplier_name)::text) ~~ '%%'::text) AND (lower((stockcover0_.package_gtin)::text) ~~ '7680380960366'::text))
  • Rows Removed by Filter: 324,578
3. 1,516.966 12,433.138 ↓ 3.3 324,595 1

WindowAgg (cost=109,313.23..1,368,881.77 rows=99,646 width=293) (actual time=1,807.665..12,433.138 rows=324,595 loops=1)

4. 626.762 4,099.677 ↓ 3.3 324,595 1

Finalize GroupAggregate (cost=109,313.23..151,207.65 rows=99,646 width=125) (actual time=1,807.597..4,099.677 rows=324,595 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
5. 605.059 3,472.915 ↓ 2.1 422,803 1

Gather Merge (cost=109,313.23..144,979.77 rows=199,292 width=125) (actual time=1,807.583..3,472.915 rows=422,803 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 386.900 2,867.856 ↓ 1.4 140,934 3 / 3

Partial GroupAggregate (cost=108,313.21..120,976.51 rows=99,646 width=125) (actual time=1,772.347..2,867.856 rows=140,934 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
7. 2,271.422 2,480.956 ↑ 1.2 332,152 3 / 3

Sort (cost=108,313.21..109,351.18 rows=415,190 width=103) (actual time=1,772.318..2,480.956 rows=332,152 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: external merge Disk: 38,632kB
8. 209.534 209.534 ↑ 1.2 332,152 3 / 3

Parallel Seq Scan on stock_need st (cost=0.00..46,860.90 rows=415,190 width=103) (actual time=0.050..209.534 rows=332,152 loops=3)

9.          

SubPlan (for WindowAgg)

10. 324.595 1,622.975 ↑ 1.0 1 324,595

Aggregate (cost=2.42..2.43 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=324,595)

11. 316.153 1,298.380 ↓ 0.0 0 324,595

Nested Loop (cost=0.00..2.41 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=324,595)

  • Join Filter: (sl.location_settings_id = ls.id)
  • Rows Removed by Join Filter: 0
12. 973.785 973.785 ↓ 0.0 0 324,595

Seq Scan on stock_location sl (cost=0.00..1.24 rows=1 width=64) (actual time=0.003..0.003 rows=0 loops=324,595)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
13. 8.442 8.442 ↓ 3.0 3 4,221

Seq Scan on stock_location_settings ls (cost=0.00..1.16 rows=1 width=32) (actual time=0.001..0.002 rows=3 loops=4,221)

  • Filter: ((aggregation_type)::text = 'PACKAGE'::text)
  • Rows Removed by Filter: 1
14. 0.000 1,298.380 ↑ 1.0 1 324,595

Aggregate (cost=2.42..2.43 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=324,595)

15. 311.932 1,298.380 ↓ 0.0 0 324,595

Nested Loop (cost=0.00..2.41 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=324,595)

  • Join Filter: (sl_1.location_settings_id = ls_1.id)
  • Rows Removed by Join Filter: 0
16. 973.785 973.785 ↓ 0.0 0 324,595

Seq Scan on stock_location sl_1 (cost=0.00..1.24 rows=1 width=64) (actual time=0.003..0.003 rows=0 loops=324,595)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
17. 12.663 12.663 ↑ 1.0 1 4,221

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

  • Filter: ((aggregation_type)::text = 'DOSE'::text)
  • Rows Removed by Filter: 11
18. 0.000 1,298.380 ↑ 1.0 1 324,595

Aggregate (cost=2.42..2.43 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=324,595)

19. 311.932 1,298.380 ↓ 0.0 0 324,595

Nested Loop (cost=0.00..2.41 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=324,595)

  • Join Filter: (sl_2.location_settings_id = ls_2.id)
  • Rows Removed by Join Filter: 0
20. 973.785 973.785 ↓ 0.0 0 324,595

Seq Scan on stock_location sl_2 (cost=0.00..1.24 rows=1 width=64) (actual time=0.003..0.003 rows=0 loops=324,595)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
21. 12.663 12.663 ↑ 1.0 1 4,221

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=4,221)

  • Filter: ((aggregation_type)::text = 'CANISTER'::text)
  • Rows Removed by Filter: 12
22. 0.000 1,298.380 ↑ 1.0 1 324,595

Aggregate (cost=2.42..2.43 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=324,595)

23. 311.932 1,298.380 ↓ 0.0 0 324,595

Nested Loop (cost=0.00..2.41 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=324,595)

  • Join Filter: (sl_3.location_settings_id = ls_3.id)
  • Rows Removed by Join Filter: 0
24. 973.785 973.785 ↓ 0.0 0 324,595

Seq Scan on stock_location sl_3 (cost=0.00..1.24 rows=1 width=64) (actual time=0.003..0.003 rows=0 loops=324,595)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
25. 12.663 12.663 ↓ 2.0 2 4,221

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=4,221)

  • Filter: ((aggregation_type)::text = 'PRODUCTION'::text)
  • Rows Removed by Filter: 11
26. 0.000 1,298.380 ↑ 1.0 1 324,595

Aggregate (cost=2.49..2.50 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=324,595)

27. 320.374 1,298.380 ↓ 0.0 0 324,595

Nested Loop (cost=0.00..2.48 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=324,595)

  • Join Filter: (sl_4.location_settings_id = ls_4.id)
  • Rows Removed by Join Filter: 0
28. 973.785 973.785 ↓ 0.0 0 324,595

Seq Scan on stock_location sl_4 (cost=0.00..1.24 rows=1 width=64) (actual time=0.003..0.003 rows=0 loops=324,595)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
29. 4.221 4.221 ↑ 1.3 3 4,221

Seq Scan on stock_location_settings ls_4 (cost=0.00..1.20 rows=4 width=32) (actual time=0.001..0.001 rows=3 loops=4,221)

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