explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9IWP

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 4,227.069 ↑ 1.0 25 1

Limit (cost=517,762.63..517,764.69 rows=25 width=131) (actual time=4,226.610..4,227.069 rows=25 loops=1)

2. 0.007 4,227.066 ↑ 391.7 25 1

Subquery Scan on aggregated (cost=517,762.63..518,569.50 rows=9,792 width=131) (actual time=4,226.610..4,227.066 rows=25 loops=1)

3. 0.322 4,227.059 ↑ 391.7 25 1

GroupAggregate (cost=517,762.63..518,324.70 rows=9,792 width=99) (actual time=4,226.607..4,227.059 rows=25 loops=1)

  • Group Key: stock_coverage.dosage_id, stock_coverage.name_default
4. 82.154 4,226.737 ↑ 33.6 988 1

Sort (cost=517,762.63..517,845.67 rows=33,215 width=99) (actual time=4,226.591..4,226.737 rows=988 loops=1)

  • Sort Key: stock_coverage.dosage_id, stock_coverage.name_default
  • Sort Method: external merge Disk: 3,168kB
5. 35.483 4,144.583 ↓ 1.8 61,269 1

Subquery Scan on stock_coverage (cost=0.55..513,451.76 rows=33,215 width=99) (actual time=0.075..4,144.583 rows=61,269 loops=1)

  • Filter: (stock_coverage.planned_production_date > '2020-08-01'::date)
  • Rows Removed by Filter: 263,326
6. 1,224.648 4,109.100 ↓ 3.3 324,595 1

GroupAggregate (cost=0.55..512,206.19 rows=99,646 width=293) (actual time=0.052..4,109.100 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
7. 1,910.667 1,910.667 ↑ 1.0 996,455 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=0.032..1,910.667 rows=996,455 loops=1)

8.          

SubPlan (for GroupAggregate)

9. 0.000 973.785 ↑ 1.0 1 324,595

Aggregate (cost=2.38..2.38 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=324,595)

10. 321.136 973.785 ↓ 0.0 0 324,595

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

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

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

  • Filter: ((gtin)::text = (st.package_gtin)::text)
  • Rows Removed by Filter: 16
12. 3.459 3.459 ↑ 2.0 2 3,459

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

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