explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m7vr

Settings
# exclusive inclusive rows x rows loops node
1. 161,699.780 161,982.761 ↓ 6,042.3 984,356,893 1

Hash Join (cost=11,368.39..14,505.56 rows=162,912 width=4) (actual time=239.463..161,982.761 rows=984,356,893 loops=1)

  • Output: a.addon_id
  • Hash Cond: (a.product_id = p.product_id)
2. 44.045 44.045 ↑ 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=0.512..44.045 rows=53,527 loops=1)

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

Hash (cost=11,364.24..11,364.24 rows=332 width=4) (actual time=238.936..238.936 rows=54,402 loops=1)

  • Output: p.product_id
  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2425kB
4. 23.720 222.316 ↓ 163.9 54,402 1

Hash Join (cost=8,108.67..11,364.24 rows=332 width=4) (actual time=152.571..222.316 rows=54,402 loops=1)

  • Output: p.product_id
  • Inner Unique: true
  • Hash Cond: ((p_1.productsku)::text = (p.identifier)::text)
5. 31.324 198.524 ↓ 159.5 54,402 1

Hash Join (cost=8,102.21..11,356.86 rows=341 width=8) (actual time=152.486..198.524 rows=54,402 loops=1)

  • Output: p_1.productsku
  • Hash Cond: ((p_1.ticketno)::text = (d.ticket_number)::text)
6. 15.091 15.091 ↑ 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=0.025..15.091 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.363 152.109 ↑ 1.0 33,643 1

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

  • Output: d.ticket_number
  • Buckets: 65536 Batches: 1 Memory Usage: 1860kB
8. 24.230 140.746 ↑ 1.0 33,643 1

Merge Join (cost=62.86..7,681.24 rows=33,643 width=9) (actual time=98.865..140.746 rows=33,643 loops=1)

  • Output: d.ticket_number
  • Merge Cond: (d.ticket_id = t.ticket_id)
9. 15.960 15.960 ↑ 82.6 67,917 1

Index Scan using dim_tickets_pkey on scv.dim_tickets d (cost=0.43..480,878.62 rows=5,606,996 width=13) (actual time=0.027..15.960 rows=67,917 loops=1)

  • Output: d.ticket_id, d.sale_id, d.ticket_number, d.sale_date, d.sale_date_id
10. 100.556 100.556 ↑ 1.0 33,643 1

Index Only Scan using idx_ticket_xactions_ticket_id on scv.fact_ticket_transactions t (cost=0.29..2,310.16 rows=33,643 width=4) (actual time=87.083..100.556 rows=33,643 loops=1)

  • Output: t.ticket_id
  • Heap Fetches: 33643
11. 0.032 0.072 ↑ 1.0 109 1

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

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

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

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