explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dDUu

Settings
# exclusive inclusive rows x rows loops node
1. 204,003.883 208,160.008 ↓ 6,042.3 984,356,893 1

Hash Join (cost=11,368.39..14,505.56 rows=162,912 width=8) (actual time=3,546.322..208,160.008 rows=984,356,893 loops=1)

  • Output: a.addon_id, t.transaction_id
  • Hash Cond: (a.product_id = p.product_id)
2. 709.322 709.322 ↑ 1.0 53,527 1

Seq Scan on scv.fact_addons a (cost=0.00..1,172.27 rows=53,527 width=8) (actual time=99.441..709.322 rows=53,527 loops=1)

  • Output: a.addon_id, a.product_id, a.amount, a.quantity
3. 22.426 3,446.803 ↓ 163.9 54,402 1

Hash (cost=11,364.24..11,364.24 rows=332 width=8) (actual time=3,446.803..3,446.803 rows=54,402 loops=1)

  • Output: p.product_id, t.transaction_id
  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2638kB
4. 38.140 3,424.377 ↓ 163.9 54,402 1

Hash Join (cost=8,108.67..11,364.24 rows=332 width=8) (actual time=97.577..3,424.377 rows=54,402 loops=1)

  • Output: p.product_id, t.transaction_id
  • Inner Unique: true
  • Hash Cond: ((p_1.productsku)::text = (p.identifier)::text)
5. 59.537 3,379.917 ↓ 159.5 54,402 1

Hash Join (cost=8,102.21..11,356.86 rows=341 width=12) (actual time=90.805..3,379.917 rows=54,402 loops=1)

  • Output: t.transaction_id, p_1.productsku
  • Hash Cond: ((p_1.ticketno)::text = (dt.ticket_number)::text)
6. 3,259.345 3,259.345 ↑ 1.1 53,527 1

Index Scan using "index_etl.products_on_etl_batch_file_id" on etl.products p_1 (cost=0.44..3,038.63 rows=56,811 width=17) (actual time=29.457..3,259.345 rows=53,527 loops=1)

  • Output: p_1.id, p_1.sale_id, p_1.ticketno, p_1.transaction_date, p_1.item_quantity, p_1.item_single_value, p_1.productsku, p_1.family_name, p_1.type_description, p_1.product_description, p_1.product_serial, p_1.from_date, p_1.to_date, p_1.etl_batch_file_id, p_1.imported, p_1.created_at, p_1.updated_at, p_1.customer_id
  • Index Cond: (p_1.etl_batch_file_id = 509)
7. 11.051 61.035 ↑ 1.0 33,643 1

Hash (cost=7,681.24..7,681.24 rows=33,643 width=13) (actual time=61.035..61.035 rows=33,643 loops=1)

  • Output: t.transaction_id, dt.ticket_number
  • Buckets: 65536 Batches: 1 Memory Usage: 1991kB
8. 23.752 49.984 ↑ 1.0 33,643 1

Merge Join (cost=62.86..7,681.24 rows=33,643 width=13) (actual time=10.932..49.984 rows=33,643 loops=1)

  • Output: t.transaction_id, dt.ticket_number
  • Inner Unique: true
  • Merge Cond: (t.ticket_id = dt.ticket_id)
9. 11.220 11.220 ↑ 1.0 33,643 1

Index Scan using idx_ticket_xactions_ticket_id on scv.fact_ticket_transactions t (cost=0.29..2,310.16 rows=33,643 width=8) (actual time=0.017..11.220 rows=33,643 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
10. 15.012 15.012 ↑ 82.6 67,916 1

Index Scan using dim_tickets_pkey on scv.dim_tickets dt (cost=0.43..480,878.62 rows=5,606,996 width=13) (actual time=0.016..15.012 rows=67,916 loops=1)

  • Output: dt.ticket_id, dt.sale_id, dt.ticket_number, dt.sale_date, dt.sale_date_id
11. 0.044 6.320 ↑ 1.0 109 1

Hash (cost=5.09..5.09 rows=109 width=12) (actual time=6.320..6.320 rows=109 loops=1)

  • Output: p.product_id, p.identifier
  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
12. 6.276 6.276 ↑ 1.0 109 1

Seq Scan on scv.dim_products p (cost=0.00..5.09 rows=109 width=12) (actual time=6.252..6.276 rows=109 loops=1)

  • Output: p.product_id, p.identifier
Planning time : 196.798 ms