explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B4UU

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

Limit (cost=526,925.02..526,929.14 rows=25 width=131) (actual time=4,346.723..4,350.145 rows=25 loops=1)

2. 0.006 4,350.142 ↑ 398.6 25 1

Subquery Scan on aggregated (cost=526,925.02..528,569.19 rows=9,965 width=131) (actual time=4,346.722..4,350.142 rows=25 loops=1)

3. 2.043 4,350.136 ↑ 398.6 25 1

GroupAggregate (cost=526,925.02..528,320.07 rows=9,965 width=99) (actual time=4,346.720..4,350.136 rows=25 loops=1)

  • Group Key: stock_coverage.dosage_id, stock_coverage.name_default
4. 236.943 4,348.093 ↑ 13.6 7,317 1

Sort (cost=526,925.02..527,174.13 rows=99,646 width=99) (actual time=4,346.680..4,348.093 rows=7,317 loops=1)

  • Sort Key: stock_coverage.dosage_id, stock_coverage.name_default
  • Sort Method: external merge Disk: 16,920kB
5. 48.462 4,111.150 ↓ 3.3 324,595 1

Subquery Scan on stock_coverage (cost=0.55..513,202.65 rows=99,646 width=99) (actual time=0.046..4,111.150 rows=324,595 loops=1)

6. 1,206.306 4,062.688 ↓ 3.3 324,595 1

GroupAggregate (cost=0.55..512,206.19 rows=99,646 width=293) (actual time=0.046..4,062.688 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,882.597 1,882.597 ↑ 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.029..1,882.597 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.514 ms