explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q7jX

Settings
# exclusive inclusive rows x rows loops node
1. 1.760 1,570.961 ↓ 544.0 544 1

Nested Loop (cost=419.97..98,414.17 rows=1 width=178) (actual time=11.364..1,570.961 rows=544 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
  • Join Filter: (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
2. 0.532 1,549.245 ↓ 544.0 544 1

Nested Loop (cost=419.97..98,404.65 rows=1 width=176) (actual time=11.315..1,549.245 rows=544 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,
3. 0.870 1,538.377 ↓ 544.0 544 1

Nested Loop (cost=419.55..98,398.19 rows=1 width=191) (actual time=11.295..1,538.377 rows=544 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, ppi.product_id
  • Join Filter: ((ptx.id)::text = (pt.pos_txn_id)::text)
4. 333.000 708.382 ↑ 1.6 375 1

Hash Join (cost=309.35..31,571.74 rows=585 width=93) (actual time=10.799..708.382 rows=375 loops=1)

  • Output: ptx.sales_date, ptx.status, ptx.member_disc_reversal, ptx.type, ptx.create_date_aud, ptx.id, ptx.store_id, ppi.type, ppi.pos_txn_id, ppi.product_id
  • Hash Cond: ((ppi.pos_txn_id)::text = (ptx.id)::text)
5. 372.793 372.793 ↑ 1.0 791,928 1

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

  • Output: ppi.id, ppi.create_date_aud, ppi.update_date_aud, ppi.version, ppi.item_desc, ppi.item_quantity, ppi.label, ppi.price_unit, ppi.total_discount, ppi.type, ppi.pos_txn_id, ppi.product_id
6. 0.816 2.589 ↑ 1.1 2,890 1

Hash (cost=271.37..271.37 rows=3,038 width=55) (actual time=2.589..2.589 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
7. 1.773 1.773 ↑ 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.052..1.773 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
8. 1.125 829.125 ↑ 1.0 1 375

Bitmap Heap Scan on public.pos_tx_item pt (cost=110.20..114.22 rows=1 width=116) (actual time=2.210..2.211 rows=1 loops=375)

  • 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, 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
  • Recheck Cond: (((pt.pos_txn_id)::text = (ppi.pos_txn_id)::text) AND ((pt.product_id)::text = (ppi.product_id)::text))
  • Rows Removed by Index Recheck: 1
  • Filter: ((pt.category_id)::text <> 'DEPTSTORE'::text)
  • Heap Blocks: exact=399
9. 1.125 828.000 ↓ 0.0 0 375

BitmapAnd (cost=110.20..110.20 rows=1 width=0) (actual time=2.208..2.208 rows=0 loops=375)

10. 19.875 19.875 ↑ 17.6 44 375

Bitmap Index Scan on idx_tx_itm (cost=0.00..31.67 rows=775 width=0) (actual time=0.053..0.053 rows=44 loops=375)

  • Index Cond: ((pt.pos_txn_id)::text = (ppi.pos_txn_id)::text)
11. 807.000 807.000 ↓ 3.5 10,174 375

Bitmap Index Scan on idx_txi_prodid (cost=0.00..78.28 rows=2,897 width=0) (actual time=2.152..2.152 rows=10,174 loops=375)

  • Index Cond: ((pt.product_id)::text = (ppi.product_id)::text)
12. 10.336 10.336 ↑ 1.0 1 544

Index Scan using product_pkey on public.product pr (cost=0.42..6.45 rows=1 width=39) (actual time=0.019..0.019 rows=1 loops=544)

  • Output: pr.plu_id, pr.create_date_aud, pr.update_date_aud, pr.version, pr.active, pr.category_id, pr.current_price, pr.department_code, pr.description, pr.ean13code, pr.is_tax_inclusive, pr.is_weighted, pr.item_code, pr.last_updated, pr.name, pr.plu, pr.sales_unit, pr.short_desc, pr.sku, pr.store_code, pr.vat_code, pr.vat_rate, pr.trk_point, pr.trk_sp, pr.trk_type
  • Index Cond: ((pr.plu_id)::text = (pt.product_id)::text)
  • Filter: (pr.sku IS NOT NULL)
13. 0.544 0.544 ↑ 1.0 1 544

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

  • Output: s.store_id, s.create_date_aud, s.update_date_aud, s.version, s.address, s.code, s.hotline, s.mac_address, s.name, s.legacy_store_code, s.phone_number, s.status, s.sales_date
14.          

SubPlan (for Nested Loop)

15. 0.422 19.412 ↑ 1.0 1 211

Limit (cost=8.45..8.46 rows=1 width=16) (actual time=0.092..0.092 rows=1 loops=211)

  • Output: (substr((electronic_fund_transfer.card_num)::text, 1, 6))
16. 0.211 18.990 ↑ 1.0 1 211

HashAggregate (cost=8.45..8.46 rows=1 width=16) (actual time=0.090..0.090 rows=1 loops=211)

  • Output: (substr((electronic_fund_transfer.card_num)::text, 1, 6))
  • Group Key: substr((electronic_fund_transfer.card_num)::text, 1, 6)
17. 18.779 18.779 ↑ 1.0 1 211

Index Scan using etf_trf_idx01 on public.electronic_fund_transfer (cost=0.43..8.45 rows=1 width=16) (actual time=0.088..0.089 rows=1 loops=211)

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