explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vm87

Settings
# exclusive inclusive rows x rows loops node
1. 45.679 92,947.959 ↓ 1.4 42,306 1

Hash Left Join (cost=2,653,814.74..2,970,713.67 rows=30,151 width=178) (actual time=91,949.191..92,947.959 rows=42,306 loops=1)

  • Output: s.code, ptx.sales_date, pt.pos_txn_id, ptx.status, CASE WHEN (pt.member_discount_amount = 0::double precision) THEN 'N'::text ELSE 'Y'::text END, ((((pt.discount_amount + CASE WHEN (ptx.member_disc_reversal = 0::double precision) THEN pt.member_discount_amount ELSE 0::double precision END) + pt.second_layer_discount_amount) + pt.disc_btn_amount) + pt.crm_member_discount_amount), pt.member_discount_amount, ptx.type, pt.is_voided, COALESCE(pt.sku, pr.sku), pr.is_tax_inclusive, s.code, pt.quantity, (pt.price_subtotal + pt.non_member_markup), ptx.sales_date, pr.department_code,
  • pr.vat_code, ptx.create_date_aud, CASE WHEN ((ptx.type)::text = 'RETURN'::text) THEN 'R'::character varying WHEN (ppi.type IS NULL) THEN (CASE WHEN ((((pt.discount_amount + pt.second_layer_discount_amount) + pt.disc_btn_amount) + pt.crm_member_discount_amount) = 0::double precision) THEN 'N'::text ELSE '1'::text END)::character varying ELSE ppi.type END, CASE WHEN (pt.member_discount_amount
  • = 0::double precision) THEN NULL::text ELSE (SubPlan 1) END
  • Hash Cond: (ptx.store_id = s.store_id)
  • pt.price_subtotal, pt.non_member_markup, pr.sku, pr.is_tax_inclusive, pr.department_code, pr.vat_code, ptx.sales_date, ptx.status, ptx.member_disc_reversal, ptx.type, ptx.create_date_aud, ptx.id,ptx.store_id, ppi.type
  • pt.non_member_markup, pt.order_index, pt.price_subtotal, pt.price_subtotal_inclusive, pt.price_unit, pt.product_id, pt.qty_with_member_discount, pt.qty_with_second_layer_discount, pt.quantity, pt.sales_type, pt.scan_time, pt.second_layer_discount_amount, pt.short_desc, pt.pos_txn_id, pt.description, pt.old_non_member_markup, pt.disc_btn_amount, pt.ean13code, pt.crm_member_discount_amount, pt.original_price_unit, pt.sku, pt.weight, pt.scan_date, pt.weight_barcode
2. 102.201 92,682.890 ↓ 1.4 42,306 1

Hash Join (cost=2,653,813.71..2,714,165.32 rows=30,151 width=176) (actual time=91,949.096..92,682.890 rows=42,306 loops=1)

  • Output: pt.pos_txn_id, pt.member_discount_amount, pt.discount_amount, pt.second_layer_discount_amount, pt.disc_btn_amount, pt.crm_member_discount_amount, pt.is_voided, pt.sku, pt.quantity,
  • Hash Cond: ((pt.product_id)::text = (pr.plu_id)::text)
3. 433.524 92,289.247 ↓ 1.4 42,306 1

Hash Right Join (cost=2,612,591.68..2,667,862.27 rows=30,151 width=173) (actual time=91,649.963..92,289.247 rows=42,306 loops=1)

  • Output: pt.pos_txn_id, pt.member_discount_amount, pt.discount_amount, pt.second_layer_discount_amount, pt.disc_btn_amount, pt.crm_member_discount_amount, pt.is_voided, pt.sku, pt.quantity, pt.price_subtotal, pt.non_member_markup, pt.product_id, ptx.sales_date, ptx.status, ptx.member_disc_reversal, ptx.type, ptx.create_date_aud, ptx.id, ptx.store_id, ppi.type
  • Hash Cond: (((ppi.pos_txn_id)::text = (pt.pos_txn_id)::text) AND ((ppi.product_id)::text = (pt.product_id)::text))
4. 239.566 239.566 ↑ 1.0 791,930 1

Seq Scan on public.pos_promotion_item ppi (cost=0.00..26,304.41 rows=792,341 width=38) (actual time=0.031..239.566 rows=791,930 loops=1)

  • Output: ppi.type, ppi.pos_txn_id, ppi.product_id
5. 33.129 91,616.157 ↓ 1.4 42,306 1

Hash (cost=2,611,402.41..2,611,402.41 rows=30,151 width=171) (actual time=91,616.157..91,616.157 rows=42,306 loops=1)

  • Output: pt.pos_txn_id, pt.member_discount_amount, pt.discount_amount, pt.second_layer_discount_amount, pt.disc_btn_amount, pt.crm_member_discount_amount, pt.is_voided, pt.sku,pt.quantity, pt.price_subtotal, pt.non_member_markup, pt.product_id, ptx.sales_date, ptx.status, ptx.member_disc_reversal, ptx.type, ptx.create_date_aud, ptx.id, ptx.store_id
  • Buckets: 1024 Batches: 8 (originally 4) Memory Usage: 2049kB
6. 13,028.562 91,583.028 ↓ 1.4 42,306 1

Hash Join (cost=309.35..2,611,402.41 rows=30,151 width=171) (actual time=179.686..91,583.028 rows=42,306 loops=1)

  • Output: pt.pos_txn_id, pt.member_discount_amount, pt.discount_amount, pt.second_layer_discount_amount, pt.disc_btn_amount, pt.crm_member_discount_amount, pt.is_voided, pt.sku, pt.quantity, pt.price_subtotal, pt.non_member_markup, pt.product_id, ptx.sales_date, ptx.status, ptx.member_disc_reversal, ptx.type, ptx.create_date_aud, ptx.id, ptx.store_id
  • Hash Cond: ((pt.pos_txn_id)::text = (ptx.id)::text)
7. 78,552.147 78,552.147 ↑ 1.0 40,371,985 1

Seq Scan on public.pos_tx_item pt (cost=0.00..2,355,663.69 rows=40,820,459 width=116) (actual time=0.006..78,552.147 rows=40,371,985 loops=1)

  • Output: pt.id, pt.create_date_aud, pt.update_date_aud, pt.version, pt.category_id, pt.discount_amount, pt.is_tax_inclusive, pt.is_voided, pt.member_discount_amount,
  • Filter: ((pt.category_id)::text <> 'DEPTSTORE'::text)
  • Rows Removed by Filter: 72977
8. 0.822 2.319 ↑ 1.1 2,890 1

Hash (cost=271.37..271.37 rows=3,038 width=55) (actual time=2.319..2.319 rows=2,890 loops=1)

  • Output: ptx.sales_date, ptx.status, ptx.member_disc_reversal, ptx.type, ptx.create_date_aud, ptx.id, ptx.store_id
  • Buckets: 1024 Batches: 1 Memory Usage: 272kB
9. 1.497 1.497 ↑ 1.1 2,890 1

Index Scan using idx_tx_saldat on public.pos_transaction ptx (cost=0.43..271.37 rows=3,038 width=55) (actual time=0.024..1.497 rows=2,890 loops=1)

  • Output: ptx.sales_date, ptx.status, ptx.member_disc_reversal, ptx.type, ptx.create_date_aud, ptx.id, ptx.store_id
  • Index Cond: (ptx.sales_date = to_date('20200302'::text, 'YYYYMMDD'::text))
  • Filter: ((ptx.status)::text = 'COMPLETED'::text)
  • Rows Removed by Filter: 49
10. 121.084 291.442 ↑ 1.0 384,394 1

Hash (cost=33,412.46..33,412.46 rows=384,446 width=39) (actual time=291.442..291.442 rows=384,394 loops=1)

  • Output: pr.sku, pr.is_tax_inclusive, pr.department_code, pr.vat_code, pr.plu_id
  • Buckets: 4096 Batches: 16 Memory Usage: 1725kB
11. 170.358 170.358 ↑ 1.0 384,394 1

Seq Scan on public.product pr (cost=0.00..33,412.46 rows=384,446 width=39) (actual time=0.004..170.358 rows=384,394 loops=1)

  • Output: pr.sku, pr.is_tax_inclusive, pr.department_code, pr.vat_code, pr.plu_id
  • Filter: (pr.sku IS NOT NULL)
12. 0.001 0.005 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: s.code, s.store_id
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
13. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on public.store s (cost=0.00..1.01 rows=1 width=10) (actual time=0.003..0.004 rows=1 loops=1)

  • Output: s.code, s.store_id
14.          

SubPlan (for Hash Left Join)

15. 12.905 219.385 ↑ 1.0 1 12,905

Limit (cost=8.45..8.46 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=12,905)

  • Output: (substr((electronic_fund_transfer.card_num)::text, 1, 6))
16. 12.905 206.480 ↑ 1.0 1 12,905

HashAggregate (cost=8.45..8.46 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=12,905)

  • Output: (substr((electronic_fund_transfer.card_num)::text, 1, 6))
  • Group Key: substr((electronic_fund_transfer.card_num)::text, 1, 6)
17. 193.575 193.575 ↑ 1.0 1 12,905

Index Scan using etf_trf_idx01 on public.electronic_fund_transfer (cost=0.43..8.45 rows=1 width=16) (actual time=0.015..0.015 rows=1 loops=12,905)

  • Output: substr((electronic_fund_transfer.card_num)::text, 1, 6)
  • Index Cond: ((electronic_fund_transfer.transaction_id)::text = (ptx.id)::text)