explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0H0

Settings
# exclusive inclusive rows x rows loops node
1. 36.254 1,428.024 ↓ 322.6 108,396 1

Hash Join (cost=1.93..7,743.49 rows=336 width=12) (actual time=1,351.905..1,428.024 rows=108,396 loops=1)

  • Output: t.transaction_id, p.productsku
  • Hash Cond: (t.ticket_id = d.ticket_id)
2. 20.902 39.974 ↑ 1.0 67,286 1

Group (cost=0.42..4,108.56 rows=67,286 width=8) (actual time=0.013..39.974 rows=67,286 loops=1)

  • Output: t.transaction_id, t.ticket_id
  • Group Key: t.transaction_id
3. 19.072 19.072 ↑ 1.0 67,286 1

Index Scan using fact_ticket_transactions_pkey on scv.fact_ticket_transactions t (cost=0.42..3,940.35 rows=67,286 width=8) (actual time=0.011..19.072 rows=67,286 loops=1)

  • Output: t.transaction_id, t.customer_id, t.ticket_id, t.agent_id, t.sale_date, t.sale_date_id, t.sales_channel, t.transaction_type, t.ticket_type, t.ticket_queue, t.fare_type, t.fare_value, t.adults, t.children, t.concessionary, t.young_person_coach_card, t.eurolines_young_person, t.hm_forces, t.infants, t.eurolines_seniors, t.over_sixties, t.coach_card_id, t.coach_card_consumer_type_id, t.account_id, t.distribution_type, t.ticket_email_id
4. 14.785 1,351.796 ↓ 53,435.0 53,435 1

Hash (cost=1.46..1.46 rows=1 width=12) (actual time=1,351.796..1,351.796 rows=53,435 loops=1)

  • Output: p.productsku, d.ticket_id
  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,809kB
5. 18.636 1,337.011 ↓ 53,435.0 53,435 1

Group (cost=1.40..1.41 rows=1 width=12) (actual time=1,314.657..1,337.011 rows=53,435 loops=1)

  • Output: p.productsku, d.ticket_id
  • Group Key: d.ticket_id, p.productsku
6. 56.843 1,318.375 ↓ 53,527.0 53,527 1

Sort (cost=1.40..1.40 rows=1 width=12) (actual time=1,314.655..1,318.375 rows=53,527 loops=1)

  • Output: p.productsku, d.ticket_id
  • Sort Key: d.ticket_id, p.productsku
  • Sort Method: quicksort Memory: 3,875kB
7. 67.910 1,261.532 ↓ 53,527.0 53,527 1

Nested Loop (cost=0.87..1.39 rows=1 width=12) (actual time=0.059..1,261.532 rows=53,527 loops=1)

  • Output: p.productsku, d.ticket_id
  • Inner Unique: true
8. 16.028 16.028 ↓ 53,527.0 53,527 1

Index Scan using "index_etl.products_on_etl_batch_file_id" on etl.products p (cost=0.44..0.70 rows=1 width=17) (actual time=0.021..16.028 rows=53,527 loops=1)

  • Output: p.id, p.sale_id, p.ticketno, p.transaction_date, p.item_quantity, p.item_single_value, p.productsku, p.family_name, p.type_description, p.product_description, p.product_serial, p.from_date, p.to_date, p.etl_batch_file_id, p.imported, p.created_at, p.updated_at, p.customer_id
  • Index Cond: (p.etl_batch_file_id = 509)
9. 1,177.594 1,177.594 ↑ 1.0 1 53,527

Index Scan using idx_unique_tickets on scv.dim_tickets d (cost=0.43..0.69 rows=1 width=13) (actual time=0.022..0.022 rows=1 loops=53,527)

  • Output: d.ticket_id, d.sale_id, d.ticket_number, d.sale_date, d.sale_date_id
  • Index Cond: ((d.ticket_number)::text = (p.ticketno)::text)