explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CIL5

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,653,751.22..2,334,407,203.57 rows=30,141 width=178) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,653,750.20..2,714,098.61 rows=30,141 width=176) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,612,528.16..2,667,797.75 rows=30,141 width=173) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Seq Scan on public.pos_promotion_item ppi (cost=0.00..26,304.41 rows=792,341 width=38) (actual rows= loops=)

  • Output: ppi.type, ppi.pos_txn_id, ppi.product_id
5. 0.000 0.000 ↓ 0.0

Hash (cost=2,611,340.05..2,611,340.05 rows=30,141 width=171) (actual rows= loops=)

  • 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
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=309.34..2,611,340.05 rows=30,141 width=171) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

Seq Scan on public.pos_tx_item pt (cost=0.00..2,355,607.51 rows=40,819,486 width=116) (actual rows= loops=)

  • 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)
8. 0.000 0.000 ↓ 0.0

Hash (cost=271.37..271.37 rows=3,037 width=55) (actual rows= loops=)

  • Output: ptx.sales_date, ptx.status, ptx.member_disc_reversal, ptx.type, ptx.create_date_aud, ptx.id, ptx.store_id
9. 0.000 0.000 ↓ 0.0

Index Scan using idx_tx_saldat on public.pos_transaction ptx (cost=0.43..271.37 rows=3,037 width=55) (actual rows= loops=)

  • 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)
10. 0.000 0.000 ↓ 0.0

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

  • Output: pr.sku, pr.is_tax_inclusive, pr.department_code, pr.vat_code, pr.plu_id
11. 0.000 0.000 ↓ 0.0

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

  • Output: pr.sku, pr.is_tax_inclusive, pr.department_code, pr.vat_code, pr.plu_id
  • Filter: (pr.sku IS NOT NULL)
12. 0.000 0.000 ↓ 0.0

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

  • Output: s.code, s.store_id
13. 0.000 0.000 ↓ 0.0

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

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

SubPlan (for Hash Left Join)

15. 0.000 0.000 ↓ 0.0

Limit (cost=77,359.46..77,359.47 rows=1 width=16) (actual rows= loops=)

  • Output: (substr((electronic_fund_transfer.card_num)::text, 1, 6))
16. 0.000 0.000 ↓ 0.0

HashAggregate (cost=77,359.46..77,359.47 rows=1 width=16) (actual rows= loops=)

  • Output: (substr((electronic_fund_transfer.card_num)::text, 1, 6))
  • Group Key: substr((electronic_fund_transfer.card_num)::text, 1, 6)
17. 0.000 0.000 ↓ 0.0

Seq Scan on public.electronic_fund_transfer (cost=0.00..77,359.45 rows=1 width=16) (actual rows= loops=)

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