explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hrUU

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 6.471 ↑ 1.0 25 1

Limit (cost=0.55..75,061.46 rows=25 width=293) (actual time=1.396..6.471 rows=25 loops=1)

2. 0.059 6.463 ↑ 19.9 25 1

Subquery Scan on stockcover0_ (cost=0.55..1,495,213.98 rows=498 width=293) (actual time=1.395..6.463 rows=25 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) ~~ '%'::text))
  • Rows Removed by Filter: 165
3. 0.454 6.404 ↑ 524.5 190 1

WindowAgg (cost=0.55..1,492,224.60 rows=99,646 width=293) (actual time=1.196..6.404 rows=190 loops=1)

4. 0.468 1.960 ↑ 524.5 190 1

GroupAggregate (cost=0.55..274,550.48 rows=99,646 width=125) (actual time=1.158..1.960 rows=190 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. 1.492 1.492 ↑ 4,276.6 233 1

Index Scan using idx_stock_need_complex on stock_need st (cost=0.55..248,393.53 rows=996,455 width=103) (actual time=1.116..1.492 rows=233 loops=1)

6.          

SubPlan (for WindowAgg)

7. 0.190 0.950 ↑ 1.0 1 190

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

8. 0.190 0.760 ↓ 0.0 0 190

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

  • Join Filter: (sl.location_settings_id = ls.id)
9. 0.570 0.570 ↓ 0.0 0 190

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

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on stock_location_settings ls (cost=0.00..1.16 rows=1 width=32) (never executed)

  • Filter: ((aggregation_type)::text = 'PACKAGE'::text)
11. 0.190 0.760 ↑ 1.0 1 190

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

12. 0.000 0.570 ↓ 0.0 0 190

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

  • Join Filter: (sl_1.location_settings_id = ls_1.id)
13. 0.570 0.570 ↓ 0.0 0 190

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=190)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on stock_location_settings ls_1 (cost=0.00..1.16 rows=1 width=32) (never executed)

  • Filter: ((aggregation_type)::text = 'DOSE'::text)
15. 0.000 0.760 ↑ 1.0 1 190

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

16. 0.190 0.760 ↓ 0.0 0 190

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

  • Join Filter: (sl_2.location_settings_id = ls_2.id)
17. 0.570 0.570 ↓ 0.0 0 190

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=190)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on stock_location_settings ls_2 (cost=0.00..1.16 rows=1 width=32) (never executed)

  • Filter: ((aggregation_type)::text = 'CANISTER'::text)
19. 0.000 0.760 ↑ 1.0 1 190

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

20. 0.190 0.760 ↓ 0.0 0 190

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

  • Join Filter: (sl_3.location_settings_id = ls_3.id)
21. 0.570 0.570 ↓ 0.0 0 190

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=190)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on stock_location_settings ls_3 (cost=0.00..1.16 rows=1 width=32) (never executed)

  • Filter: ((aggregation_type)::text = 'PRODUCTION'::text)
23. 0.000 0.760 ↑ 1.0 1 190

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

24. 0.190 0.760 ↓ 0.0 0 190

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

  • Join Filter: (sl_4.location_settings_id = ls_4.id)
25. 0.570 0.570 ↓ 0.0 0 190

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=190)

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 19
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on stock_location_settings ls_4 (cost=0.00..1.20 rows=4 width=32) (never executed)

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