explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d9dC

Settings
# exclusive inclusive rows x rows loops node
1. 58,863.968 9,343,799.193 ↓ 0.0 0 1

Update on public.temp_new_weekly_report_18 a (cost=2,320,790.95..3,127,335.50 rows=3,666,463 width=169) (actual time=9,343,799.193..9,343,799.193 rows=0 loops=1)

  • Buffers: shared hit=220989173 read=116025778 dirtied=765531 written=2792, temp read=555398 written=555405
2. 7,913.069 9,284,935.225 ↓ 5.5 20,022,622 1

Hash Join (cost=2,320,790.95..3,127,335.50 rows=3,666,463 width=169) (actual time=9,260,132.400..9,284,935.225 rows=20,022,622 loops=1)

  • Output: a.yr_flag, a.store_id, a.invoice_date, a.card_no, a.sys_invoice_no, a.invoice_type_id, a.brand, a.sales, a.qty, a.cust_id, a.enrollment_date, a.mem_tag, a.lfl_tag, b.min_d, a.clean_date, a.week, a.mtd, a.ytd, a.ctid, b.*
  • Inner Unique: true
  • Hash Cond: ((a.cust_id)::text = (b.cust_id)::text)
  • Buffers: shared hit=180181679 read=115642993 written=2792, temp read=555398 written=555405
3. 16,961.624 16,961.624 ↓ 1.0 26,416,033 1

Seq Scan on public.temp_new_weekly_report_18 a (cost=0.00..737,200.32 rows=26,416,032 width=119) (actual time=0.013..16,961.624 rows=26,416,033 loops=1)

  • Output: a.yr_flag, a.store_id, a.invoice_date, a.card_no, a.sys_invoice_no, a.invoice_type_id, a.brand, a.sales, a.qty, a.cust_id, a.enrollment_date, a.mem_tag, a.lfl_tag, a.clean_date, a.week, a.mtd, a.ytd, a.ctid
  • Buffers: shared hit=2 read=473038 written=2729
4. 1,181.943 9,260,060.532 ↓ 154.6 2,941,162 1

Hash (cost=2,320,553.13..2,320,553.13 rows=19,025 width=68) (actual time=9,260,060.532..9,260,060.532 rows=2,941,162 loops=1)

  • Output: b.min_d, b.*, b.cust_id
  • Buckets: 4194304 (originally 32768) Batches: 1 (originally 1) Memory Usage: 331988kB
  • Buffers: shared hit=180181677 read=115169955 written=63, temp read=555398 written=555405
5. 584.202 9,258,878.589 ↓ 154.6 2,941,162 1

Subquery Scan on b (cost=2,319,839.70..2,320,553.13 rows=19,025 width=68) (actual time=9,157,267.083..9,258,878.589 rows=2,941,162 loops=1)

  • Output: b.min_d, b.*, b.cust_id
  • Buffers: shared hit=180181677 read=115169955 written=63, temp read=555398 written=555405
6. 25,338.158 9,258,294.387 ↓ 154.6 2,941,162 1

GroupAggregate (cost=2,319,839.70..2,320,362.88 rows=19,025 width=54) (actual time=9,157,267.070..9,258,294.387 rows=2,941,162 loops=1)

  • Output: xref.cust_id, min(pos.invoice_date), sum(item.quantity)
  • Group Key: xref.cust_id
  • Filter: (sum(item.quantity) > '0'::numeric)
  • Rows Removed by Filter: 294
  • Buffers: shared hit=180181677 read=115169955 written=63, temp read=555398 written=555405
7.          

Initplan (for GroupAggregate)

8. 1.687 1.687 ↑ 1.0 1 1

Seq Scan on public.weekly_report_parameter (cost=0.00..1.01 rows=1 width=4) (actual time=1.687..1.687 rows=1 loops=1)

  • Output: weekly_report_parameter.week_end_date
  • Buffers: shared read=1
9. 511,183.432 9,232,954.542 ↓ 6,119.7 116,426,595 1

Sort (cost=2,319,838.69..2,319,886.25 rows=19,025 width=27) (actual time=9,157,267.024..9,232,954.542 rows=116,426,595 loops=1)

  • Output: xref.cust_id, pos.invoice_date, item.quantity
  • Sort Key: xref.cust_id
  • Sort Method: external merge Disk: 4443184kB
  • Buffers: shared hit=180181677 read=115169955 written=63, temp read=555398 written=555405
10. 33,914.844 8,721,771.110 ↓ 6,119.7 116,426,595 1

Nested Loop (cost=1,426,641.88..2,318,486.43 rows=19,025 width=27) (actual time=38,440.343..8,721,771.110 rows=116,426,595 loops=1)

  • Output: xref.cust_id, pos.invoice_date, item.quantity
  • Buffers: shared hit=180181677 read=115169954 written=63
11. 6,414.542 1,464,468.641 ↓ 2,877.7 32,103,945 1

Nested Loop (cost=1,426,633.33..2,106,620.64 rows=11,156 width=41) (actual time=38,436.117..1,464,468.641 rows=32,103,945 loops=1)

  • Output: pos.invoice_date, pos.sys_invoice_no, xref.cust_id
  • Buffers: shared hit=29954552 read=25065759 written=22
12. 6,897.954 61,363.432 ↓ 97.0 4,549,481 1

Hash Join (cost=1,426,628.14..1,768,369.66 rows=46,888 width=52) (actual time=38,430.839..61,363.432 rows=4,549,481 loops=1)

  • Output: xref.cust_id, xref.card_no, crm.loyalty_card_no
  • Hash Cond: ((xref.card_no)::text = (crm.loyalty_card_no)::text)
  • Buffers: shared hit=1852 read=1045782
13. 10,429.578 39,000.393 ↓ 24.3 4,549,992 1

Hash Join (cost=806,610.13..1,146,710.57 rows=187,552 width=35) (actual time=22,957.166..39,000.393 rows=4,549,992 loops=1)

  • Output: xref.cust_id, xref.card_no
  • Inner Unique: true
  • Hash Cond: ((xref.cust_id)::text = (temp_new_weekly_report_18.cust_id)::text)
  • Buffers: shared hit=1496 read=637404
14. 5,614.400 5,614.400 ↓ 1.0 11,237,898 1

Seq Scan on public.t_crm_xref_cust_card xref (cost=0.00..310,601.40 rows=11,237,722 width=35) (actual time=0.037..5,614.400 rows=11,237,898 loops=1)

  • Output: xref.card_no, xref.cust_id, xref.format_id, xref.card_type_id, xref.card_holder_type, xref.created_by, xref.created_on, xref.modified_by, xref.modified_on, xref.load_date
  • Filter: ((xref.card_holder_type)::text = 'P'::text)
  • Rows Removed by Filter: 341414
  • Buffers: shared hit=1495 read=164365
15. 727.477 22,956.415 ↓ 28.4 2,941,543 1

Hash (cost=805,314.08..805,314.08 rows=103,684 width=18) (actual time=22,956.415..22,956.415 rows=2,941,543 loops=1)

  • Output: temp_new_weekly_report_18.cust_id
  • Buckets: 4194304 (originally 131072) Batches: 1 (originally 1) Memory Usage: 176771kB
  • Buffers: shared hit=1 read=473039
16. 7,155.198 22,228.938 ↓ 28.4 2,941,544 1

HashAggregate (cost=803,240.40..804,277.24 rows=103,684 width=18) (actual time=21,442.112..22,228.938 rows=2,941,544 loops=1)

  • Output: temp_new_weekly_report_18.cust_id
  • Group Key: temp_new_weekly_report_18.cust_id
  • Buffers: shared hit=1 read=473039
17. 15,073.740 15,073.740 ↓ 1.0 26,416,033 1

Seq Scan on public.temp_new_weekly_report_18 (cost=0.00..737,200.32 rows=26,416,032 width=18) (actual time=0.014..15,073.740 rows=26,416,033 loops=1)

  • Output: temp_new_weekly_report_18.yr_flag, temp_new_weekly_report_18.store_id, temp_new_weekly_report_18.invoice_date, temp_new_weekly_report_18.card_no, temp_new_weekly_report_18.sys_invoice_no, temp_new_weekly_report_18.invoice_type_id, temp_new_weekly_report_18.brand, temp_new_weekly_report_18.sales, temp_new_weekly_report_18.qty, temp_new_weekly_report_18.cust_id, temp_new_weekly_report_18.enrollment_date, temp_new_weekly_report_18.mem_tag, temp_new_weekly_report_18.lfl_tag, temp_new_weekly_report_18.first_purchase_date, temp_new_weekly_report_18.clean_date, temp_new_weekly_report_18.week, temp_new_weekly_report_18.mtd, temp_new_weekly_report_18.ytd
  • Buffers: shared hit=1 read=473039
18. 4,002.474 15,465.085 ↓ 4.0 11,646,986 1

Hash (cost=583,590.19..583,590.19 rows=2,914,226 width=17) (actual time=15,465.085..15,465.085 rows=11,646,986 loops=1)

  • Output: crm.loyalty_card_no
  • Buckets: 16777216 (originally 4194304) Batches: 1 (originally 1) Memory Usage: 688399kB
  • Buffers: shared hit=356 read=408378
19. 11,462.545 11,462.611 ↓ 4.0 11,646,986 1

Seq Scan on public.t_crm_loyalty_card crm (cost=4.62..583,590.19 rows=2,914,226 width=17) (actual time=0.113..11,462.611 rows=11,646,986 loops=1)

  • Output: crm.loyalty_card_no
  • Filter: ((NOT (hashed SubPlan 5)) AND (NOT (hashed SubPlan 6)))
  • Rows Removed by Filter: 9918
  • Buffers: shared hit=356 read=408378
20.          

SubPlan (for Seq Scan)

21. 0.031 0.042 ↑ 1.0 67 1

HashAggregate (cost=1.84..2.51 rows=67 width=5) (actual time=0.035..0.042 rows=67 loops=1)

  • Output: t_other_stores_1.store_id
  • Group Key: t_other_stores_1.store_id
  • Buffers: shared hit=1
22. 0.011 0.011 ↑ 1.0 67 1

Seq Scan on public.t_other_stores t_other_stores_1 (cost=0.00..1.67 rows=67 width=5) (actual time=0.007..0.011 rows=67 loops=1)

  • Output: t_other_stores_1.store_id
  • Buffers: shared hit=1
23. 0.015 0.024 ↑ 1.0 38 1

HashAggregate (cost=1.47..1.85 rows=38 width=34) (actual time=0.020..0.024 rows=38 loops=1)

  • Output: zudio_stores_1.store_id
  • Group Key: zudio_stores_1.store_id
  • Buffers: shared hit=1
24. 0.009 0.009 ↑ 1.0 38 1

Seq Scan on public.zudio_stores zudio_stores_1 (cost=0.00..1.38 rows=38 width=34) (actual time=0.007..0.009 rows=38 loops=1)

  • Output: zudio_stores_1.store_id
  • Buffers: shared hit=1
25. 1,396,690.603 1,396,690.667 ↓ 3.5 7 4,549,481

Index Scan using idx_card_no_t_pos_invoice on public.t_pos_invoice pos (cost=5.19..7.19 rows=2 width=39) (actual time=0.063..0.307 rows=7 loops=4,549,481)

  • Output: pos.mth_id, pos.day_id, pos.sys_invoice_no, pos.invoice_no, pos.invoice_type_id, pos.invoice_date, pos.invoice_time, pos.invoice_end_time, pos.format_id, pos.store_id, pos.till_no, pos.shift_no, pos.channel_id, pos.cashier_id, pos.card_no, pos.card_type, pos.loyalty_tier_code, pos.discount_amount, pos.manual_disc_amount, pos.tender_disc_amount, pos.total_qty, pos.total_weight, pos.total_amount, pos.amount_received, pos.amount_returned, pos.tax_amount, pos.status, pos.bill_on_hold_flg, pos.bill_on_hold_no, pos.bill_void_flg, pos.print_count, pos.parent_invoice_no, pos.manual_invoice_no, pos.adv_invoice_no, pos.proforma_invoice_no, pos.gate_pass_no, pos.comments, pos.created_by, pos.created_on, pos.modified_by, pos.modified_on, pos.load_date
  • Index Cond: ((pos.card_no)::text = (xref.card_no)::text)
  • Filter: ((pos.bill_void_flg IS NULL) AND (pos.invoice_type_id = ANY ('{1,4}'::integer[])) AND (pos.invoice_date <= $0) AND (NOT (hashed SubPlan 2)) AND (NOT (hashed SubPlan 3)) AND (pos.status = 'A'::bpchar))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=29952700 read=24019977 written=22
26.          

SubPlan (for Index Scan)

27. 0.030 0.041 ↑ 1.0 67 1

HashAggregate (cost=1.84..2.51 rows=67 width=5) (actual time=0.033..0.041 rows=67 loops=1)

  • Output: t_other_stores.store_id
  • Group Key: t_other_stores.store_id
  • Buffers: shared hit=1
28. 0.011 0.011 ↑ 1.0 67 1

Seq Scan on public.t_other_stores (cost=0.00..1.67 rows=67 width=5) (actual time=0.008..0.011 rows=67 loops=1)

  • Output: t_other_stores.store_id
  • Buffers: shared hit=1
29. 0.014 0.023 ↑ 1.0 38 1

HashAggregate (cost=1.47..1.85 rows=38 width=34) (actual time=0.019..0.023 rows=38 loops=1)

  • Output: zudio_stores.store_id
  • Group Key: zudio_stores.store_id
  • Buffers: shared hit=1
30. 0.009 0.009 ↑ 1.0 38 1

Seq Scan on public.zudio_stores (cost=0.00..1.38 rows=38 width=34) (actual time=0.007..0.009 rows=38 loops=1)

  • Output: zudio_stores.store_id
  • Buffers: shared hit=1
31. 7,223,387.581 7,223,387.625 ↑ 4.0 4 32,103,945

Index Scan using idx_invoice_no_t_pos_invoice_item on public.t_pos_invoice_item item (cost=8.55..18.83 rows=16 width=24) (actual time=0.103..0.225 rows=4 loops=32,103,945)

  • Output: item.sys_invoice_no, item.id, item.item_code, item.quantity, item.free_good_quantity, item.weight, item.mrp, item.base_price, item.invoice_discount, item.item_lvl_manual_disc, item.item_promo_id, item.item_promo_discount, item.group_promo_id, item.group_promo_discount, item.bill_promo_id, item.bill_promo_discount, item.innov_promo_id, item.innov_promo_discount, item.sale_price, item.total_amount, item.reason_code, item.consign_slip_no, item.price_override_flg, item.prev_price, item.item_void_flg, item.gift_receipt_qty, item.item_scanned_flg, item.extra_points, item.multi_points, item.created_by, item.created_on, item.modified_by, item.modified_on, item.category_id, item.is_soft_goods, item.is_packaged_food, item.is_infants, item.is_mens_nuon, item.is_junior_boys, item.is_womens_ethnic, item.is_accessories, item.is_richmond, item.is_westside_formal, item.is_personal_care, item.is_gia, item.is_junior_girls, item.is_innerwear, item.is_kids_license, item.is_gymwear, item.is_fresh_chilled_food, item.is_brands_jewellery, item.is_kids_inner_wear, item.is_kids_ethnic, item.is_nuon_girls, item.is_kids_urban_angel, item.is_home_care, item.is_women_western_wear, item.is_footwear_mens, item.is_mens_ethnic, item.is_womens_inner_wear, item.is_food_staples, item.is_brands_pen, item.is_brands_playshop, item.is_footwear_womens, item.is_footwear_kids, item.is_sleepwear, item.is_women_western_wear_wardrobe, item.is_mens_westsport, item.is_brands_cosmetics_fragrances, item.is_westside_casual, item.is_51hl, item.is_brands_designer_pret, item.is_footwear_brands, item.is_mens_inner_wear, item.is_91fo, item.is_fem_care, item.is_brands_apparel, item.is_hard_goods, item.is_senior_boys, item.is_ascot, item.is_other, item.load_date, item.bday_discount
  • Index Cond: ((item.sys_invoice_no)::text = (pos.sys_invoice_no)::text)
  • Filter: (NOT (hashed SubPlan 4))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=150227125 read=90104195 written=41
32.          

SubPlan (for Index Scan)

33. 0.044 0.044 ↑ 1.0 398 1

Seq Scan on public.t_cat_not_used (cost=0.00..6.98 rows=398 width=9) (actual time=0.010..0.044 rows=398 loops=1)

  • Output: t_cat_not_used.category_id
  • Buffers: shared hit=3
Planning time : 5.965 ms