explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7lCa : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #03De; plan #Wimp; plan #SMS4; plan #hfoP; plan #jlvR

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 214.015 22,901.807 ↑ 1.0 1 1

GroupAggregate (cost=16,293.29..16,293.34 rows=1 width=81) (actual time=22,901.807..22,901.807 rows=1 loops=1)

  • Output: ss.validity, sum((o.amount * ssp.cases)), sum((o.amount * ssp.displays)), sum(COALESCE(((o.amount * ssp.display_count) * ssp.pack), 0)), sum(COALESCE((((o.amount)::numeric * ssp.deal_unit_cost) * (ssp.pack)::numeric), '0'::numeric)), count(DISTINCT ssp.product_id), count(DISTINCT ssp.brand_id), count(o.id)
  • Group Key: ss.validity
2. 136.530 22,687.792 ↓ 77,192.0 77,192 1

Sort (cost=16,293.29..16,293.29 rows=1 width=75) (actual time=22,664.566..22,687.792 rows=77,192 loops=1)

  • Output: ss.validity, o.amount, ssp.cases, ssp.displays, ssp.display_count, ssp.pack, ssp.deal_unit_cost, ssp.product_id, ssp.brand_id, o.id
  • Sort Key: ss.validity
  • Sort Method: external merge Disk: 6,992kB
3. 119.801 22,551.262 ↓ 77,192.0 77,192 1

Nested Loop (cost=25.37..16,293.28 rows=1 width=75) (actual time=2.720..22,551.262 rows=77,192 loops=1)

  • Output: ss.validity, o.amount, ssp.cases, ssp.displays, ssp.display_count, ssp.pack, ssp.deal_unit_cost, ssp.product_id, ssp.brand_id, o.id
  • Inner Unique: true
4. 75.764 22,199.717 ↓ 25,749.3 77,248 1

Nested Loop (cost=25.09..16,292.39 rows=3 width=139) (actual time=2.711..22,199.717 rows=77,248 loops=1)

  • Output: o.amount, o.id, o.wave_id, ss.validity, ss.survey_id, s.id, ssp.cases, ssp.displays, ssp.display_count, ssp.pack, ssp.deal_unit_cost, ssp.product_id, ssp.brand_id, ssp.survey_id
  • Join Filter: (ss.store_id = o.store_id)
5. 139.764 820.827 ↓ 1,379.4 91,039 1

Nested Loop (cost=24.67..16,092.13 rows=66 width=135) (actual time=2.180..820.827 rows=91,039 loops=1)

  • Output: s.id, ss.validity, ss.store_id, ss.survey_id, ssp.cases, ssp.displays, ssp.display_count, ssp.pack, ssp.deal_unit_cost, ssp.product_id, ssp.brand_id, ssp.survey_id, ssp.store_id
  • Inner Unique: true
6. 60.692 134.829 ↓ 103.3 91,039 1

Nested Loop (cost=24.38..15,697.32 rows=881 width=102) (actual time=2.162..134.829 rows=91,039 loops=1)

  • Output: s.id, ssp.cases, ssp.displays, ssp.display_count, ssp.pack, ssp.deal_unit_cost, ssp.product_id, ssp.brand_id, ssp.survey_id, ssp.store_id
7. 0.013 0.013 ↑ 1.0 1 1

Index Scan using surveys_pkey on public.surveys s (cost=0.14..8.16 rows=1 width=24) (actual time=0.010..0.013 rows=1 loops=1)

  • Output: s.id, s.period, s.book, s.year, s.status, s.starts_at, s.ends_at, s.tag, s.title, s.enabled, s.info, s.opened_at, s.closed_at, s.event_type, s.aom_comments, s.distribution_center_id, s.created_at, s.updated_at, s.current_mailing_id, s.product_list_template_id, s.auto_open, s.auto_close, s.help_content, s.closing_at
  • Index Cond: (s.id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid)
8. 73.776 74.124 ↓ 110.6 91,039 1

Bitmap Heap Scan on public.survey_store_products ssp (cost=24.24..15,680.92 rows=823 width=94) (actual time=2.142..74.124 rows=91,039 loops=1)

  • Output: ssp.starts_at, ssp.survey_id, ssp.product_id, ssp.store_id, ssp.brand_id, ssp.retail_chain_id, ssp.distribution_center_id, ssp.um, ssp.cases, ssp.displays, ssp.display_count, ssp.pack, ssp.deal_unit_cost
  • Recheck Cond: ((ssp.starts_at = s.starts_at) AND (ssp.survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid))
  • Rows Removed by Index Recheck: 9,713
  • Heap Blocks: lossy=2,048
9. 0.348 0.348 ↓ 3.3 20,480 1

Bitmap Index Scan on index_survey_store_products_brin (cost=0.00..24.03 rows=6,271 width=0) (actual time=0.348..0.348 rows=20,480 loops=1)

  • Index Cond: ((ssp.starts_at = s.starts_at) AND (ssp.survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid))
10. 546.234 546.234 ↑ 1.0 1 91,039

Index Scan using index_survey_stores_on_store_id on public.survey_stores ss (cost=0.29..0.45 rows=1 width=33) (actual time=0.006..0.006 rows=1 loops=91,039)

  • Output: ss.id, ss.survey_id, ss.store_id, ss.created_at, ss.updated_at, ss.status, ss.updated_by_id, ss.new_at, ss.in_progress_at, ss.done_at, ss.validity
  • Index Cond: (ss.store_id = ssp.store_id)
  • Filter: (ss.survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid)
  • Rows Removed by Filter: 1
11. 21,303.126 21,303.126 ↑ 1.0 1 91,039

Index Scan using index_orders_on_product_id on public.orders o (cost=0.42..3.02 rows=1 width=68) (actual time=0.173..0.234 rows=1 loops=91,039)

  • Output: o.id, o.amount, o.wave_id, o.product_id, o.store_id, o.created_by_id, o.updated_by_id, o.created_at, o.updated_at
  • Index Cond: (o.product_id = ssp.product_id)
  • Filter: ((o.amount > 0) AND (ssp.store_id = o.store_id))
  • Rows Removed by Filter: 312
12. 231.744 231.744 ↑ 1.0 1 77,248

Index Scan using waves_pkey on public.waves w (cost=0.27..0.29 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=77,248)

  • Output: w.id, w.title, w.number, w.shipped_at, w.survey_id, w.created_at, w.updated_at, w.description, w.promo_starts_at, w.promo_ends_at
  • Index Cond: (w.id = o.wave_id)
  • Filter: (w.survey_id = '7eb6b6de-76f5-478c-b277-eeb3685752d9'::uuid)
  • Rows Removed by Filter: 0
Planning time : 8.507 ms
Execution time : 22,905.042 ms