explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WWUH

Settings
# exclusive inclusive rows x rows loops node
1. 9.548 84.032 ↓ 2.4 25,831 1

Hash Join (cost=10,958.74..21,257.34 rows=10,839 width=1,792) (actual time=46.775..84.032 rows=25,831 loops=1)

  • Output: prescriptions.id, prescriptions.created_at, prescriptions.updated_at, prescriptions.patient_id, prescriptions.practice_id, prescriptions.quantity, prescriptions.refill, prescriptions.directions, prescriptions.daw, prescriptions.prescriber_id, prescriptions.pharmacy_id, prescriptions.filled_at, prescriptions.dos, prescriptions.deleted_at, prescriptions.batch, prescriptions.rx_number, prescriptions.ml_id, prescriptions.pharmacist_notes, prescriptions.cancel_reason, prescriptions.refill_no, prescriptions.ref_id, prescriptions.cogs, prescriptions.avail_delivery_windows, prescriptions.client_info, prescriptions.patient_info, prescriptions.practice_info, prescriptions.prescriber_info, prescriptions.pharmacy_info, prescriptions.expires_at, prescriptions.auto_refill, prescriptions.is_auto_refill, prescriptions.total_price, prescriptions.delivery_time_pref, prescriptions.written_at, prescriptions.quantity_dispensed, prescriptions.delivery_windows, prescriptions.form_id, prescriptions.state, prescriptions.deliver_to, prescriptions.auto_gen, prescriptions.origin, prescriptions.rx_type, prescriptions.deliver_by, prescriptions.note_id, prescriptions.bill_to, prescriptions.order_id, prescriptions.print_count, prescriptions.quantity_value, prescriptions.quantity_unit_id, prescriptions.quantity_dispensed_value, prescriptions.quantity_dispensed_unit_id, prescriptions.practice_discount, prescriptions.should_refill, prescriptions.contact_attempts_count, prescriptions.rx_chain_id, prescriptions.declined_refills_at
  • Inner Unique: true
  • Hash Cond: (prescriptions.filled_at = "ANY_subquery".max)
  • Buffers: shared hit=19608
2. 27.733 27.733 ↓ 1.0 25,852 1

Seq Scan on public.prescriptions (cost=0.00..10,110.73 rows=25,634 width=1,792) (actual time=0.017..27.733 rows=25,852 loops=1)

  • Output: prescriptions.id, prescriptions.created_at, prescriptions.updated_at, prescriptions.patient_id, prescriptions.practice_id, prescriptions.quantity, prescriptions.refill, prescriptions.directions, prescriptions.daw, prescriptions.prescriber_id, prescriptions.pharmacy_id, prescriptions.filled_at, prescriptions.dos, prescriptions.deleted_at, prescriptions.batch, prescriptions.rx_number, prescriptions.ml_id, prescriptions.pharmacist_notes, prescriptions.cancel_reason, prescriptions.refill_no, prescriptions.ref_id, prescriptions.cogs, prescriptions.avail_delivery_windows, prescriptions.client_info, prescriptions.patient_info, prescriptions.practice_info, prescriptions.prescriber_info, prescriptions.pharmacy_info, prescriptions.expires_at, prescriptions.auto_refill, prescriptions.is_auto_refill, prescriptions.total_price, prescriptions.delivery_time_pref, prescriptions.written_at, prescriptions.quantity_dispensed, prescriptions.delivery_windows, prescriptions.form_id, prescriptions.state, prescriptions.deliver_to, prescriptions.auto_gen, prescriptions.origin, prescriptions.rx_type, prescriptions.deliver_by, prescriptions.note_id, prescriptions.bill_to, prescriptions.order_id, prescriptions.print_count, prescriptions.quantity_value, prescriptions.quantity_unit_id, prescriptions.quantity_dispensed_value, prescriptions.quantity_dispensed_unit_id, prescriptions.practice_discount, prescriptions.should_refill, prescriptions.contact_attempts_count, prescriptions.rx_chain_id, prescriptions.declined_refills_at
  • Filter: ((prescriptions.deleted_at IS NULL) AND (prescriptions.filled_at IS NOT NULL))
  • Rows Removed by Filter: 4821
  • Buffers: shared hit=9804
3. 0.119 46.751 ↓ 3.8 764 1

Hash (cost=10,956.24..10,956.24 rows=200 width=4) (actual time=46.751..46.751 rows=764 loops=1)

  • Output: "ANY_subquery".max
  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
  • Buffers: shared hit=9804
4. 3.544 46.632 ↓ 3.8 765 1

HashAggregate (cost=10,954.24..10,956.24 rows=200 width=4) (actual time=46.526..46.632 rows=765 loops=1)

  • Output: "ANY_subquery".max
  • Group Key: "ANY_subquery".max
  • Buffers: shared hit=9804
5. 2.623 43.088 ↑ 1.7 18,556 1

Subquery Scan on ANY_subquery (cost=10,264.10..10,877.56 rows=30,673 width=4) (actual time=37.105..43.088 rows=18,556 loops=1)

  • Output: "ANY_subquery".max
  • Buffers: shared hit=9804
6. 11.723 40.465 ↑ 1.7 18,556 1

HashAggregate (cost=10,264.10..10,570.83 rows=30,673 width=8) (actual time=37.104..40.465 rows=18,556 loops=1)

  • Output: max(prescriptions_1.filled_at), (COALESCE(prescriptions_1.rx_chain_id, prescriptions_1.id))
  • Group Key: COALESCE(prescriptions_1.rx_chain_id, prescriptions_1.id)
  • Buffers: shared hit=9804
7. 28.742 28.742 ↑ 1.0 30,673 1

Seq Scan on public.prescriptions prescriptions_1 (cost=0.00..10,110.73 rows=30,673 width=8) (actual time=0.004..28.742 rows=30,673 loops=1)

  • Output: COALESCE(prescriptions_1.rx_chain_id, prescriptions_1.id), prescriptions_1.filled_at
  • Buffers: shared hit=9804