explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jJSu

Settings
# exclusive inclusive rows x rows loops node
1. 70,436.079 13,817,369.697 ↓ 0.0 0 1

Update on public.temp_new_weekly_report_18 a (cost=5,796,504.36..10,061,997.92 rows=4,135,440 width=169) (actual time=13,817,369.697..13,817,369.697 rows=0 loops=1)

  • Buffers: shared hit=228,677,854 read=126,154,823 dirtied=836,813 written=416,996, temp read=568,802 written=568,809
2. 8,349.301 13,746,933.618 ↓ 5.2 21,613,143 1

Hash Join (cost=5,796,504.36..10,061,997.92 rows=4,135,440 width=169) (actual time=13,699,641.962..13,746,933.618 rows=21,613,143 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=184,192,366 read=125,733,781 written=416,996, temp read=568,802 written=568,809
3. 170,162.017 170,162.017 ↑ 1.0 28,527,397 1

Seq Scan on public.temp_new_weekly_report_18 a (cost=0.00..4,190,589.12 rows=28,534,112 width=119) (actual time=104.702..170,162.017 rows=28,527,397 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 read=3,905,248 written=3,284
4. 1,318.762 13,568,422.300 ↓ 159.0 3,059,412 1

Hash (cost=5,796,263.87..5,796,263.87 rows=19,239 width=68) (actual time=13,568,422.300..13,568,422.300 rows=3,059,412 loops=1)

  • Output: b.min_d, b.*, b.cust_id
  • Buckets: 4,194,304 (originally 32768) Batches: 1 (originally 1) Memory Usage: 344,046kB
  • Buffers: shared hit=184,192,366 read=121,828,533 written=413,712, temp read=568,802 written=568,809
5. 607.511 13,567,103.538 ↓ 159.0 3,059,412 1

Subquery Scan on b (cost=5,795,542.41..5,796,263.87 rows=19,239 width=68) (actual time=13,458,396.762..13,567,103.538 rows=3,059,412 loops=1)

  • Output: b.min_d, b.*, b.cust_id
  • Buffers: shared hit=184,192,366 read=121,828,533 written=413,712, temp read=568,802 written=568,809
6. 26,153.316 13,566,496.027 ↓ 159.0 3,059,412 1

GroupAggregate (cost=5,795,542.41..5,796,071.48 rows=19,239 width=54) (actual time=13,458,396.749..13,566,496.027 rows=3,059,412 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: 316
  • Buffers: shared hit=184,192,366 read=121,828,533 written=413,712, temp read=568,802 written=568,809
7.          

Initplan (for GroupAggregate)

8. 1.099 1.099 ↑ 1.0 1 1

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

  • Output: weekly_report_parameter.week_end_date
  • Buffers: shared read=1
9. 531,206.506 13,540,341.612 ↓ 6,197.6 119,236,550 1

Sort (cost=5,795,541.40..5,795,589.50 rows=19,239 width=27) (actual time=13,458,396.723..13,540,341.612 rows=119,236,550 loops=1)

  • Output: xref.cust_id, pos.invoice_date, item.quantity
  • Sort Key: xref.cust_id
  • Sort Method: external merge Disk: 4,550,416kB
  • Buffers: shared hit=184,192,366 read=121,828,533 written=413,712, temp read=568,802 written=568,809
10. 48,154.008 13,009,135.106 ↓ 6,197.6 119,236,550 1

Nested Loop (cost=4,890,911.13..5,794,172.38 rows=19,239 width=27) (actual time=324,846.296..13,009,135.106 rows=119,236,550 loops=1)

  • Output: xref.cust_id, pos.invoice_date, item.quantity
  • Buffers: shared hit=184,192,365 read=121,828,533 written=413,712
11. 10,813.598 3,253,031.418 ↓ 2,916.1 32,908,304 1

Nested Loop (cost=4,890,902.58..5,576,138.57 rows=11,285 width=41) (actual time=324,739.234..3,253,031.418 rows=32,908,304 loops=1)

  • Output: pos.invoice_date, pos.sys_invoice_no, xref.cust_id
  • Buffers: shared hit=30,722,502 read=29,060,419 written=89,007
12. 7,526.664 351,343.724 ↓ 99.1 4,708,264 1

Hash Join (cost=4,890,897.39..5,233,364.00 rows=47,508 width=52) (actual time=324,690.591..351,343.724 rows=4,708,264 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=3 read=4,485,459 written=349
13. 12,640.453 295,946.892 ↓ 24.8 4,708,505 1

Hash Join (cost=4,265,169.20..4,605,973.04 rows=190,031 width=35) (actual time=276,813.052..295,946.892 rows=4,708,505 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=1 read=4,071,458 written=349
14. 6,579.523 6,579.523 ↑ 1.0 11,260,513 1

Seq Scan on public.t_crm_xref_cust_card xref (cost=0.00..311,235.01 rows=11,264,305 width=35) (actual time=85.903..6,579.523 rows=11,260,513 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: 341,471
  • Buffers: shared read=166,211 written=349
15. 795.281 276,726.916 ↓ 30.6 3,059,813 1

Hash (cost=4,263,921.20..4,263,921.20 rows=99,840 width=18) (actual time=276,726.915..276,726.916 rows=3,059,813 loops=1)

  • Output: temp_new_weekly_report_18.cust_id
  • Buckets: 4,194,304 (originally 131072) Batches: 1 (originally 1) Memory Usage: 182,591kB
  • Buffers: shared hit=1 read=3,905,247
16. 8,613.262 275,931.635 ↓ 30.6 3,059,814 1

HashAggregate (cost=4,261,924.40..4,262,922.80 rows=99,840 width=18) (actual time=274,929.335..275,931.635 rows=3,059,814 loops=1)

  • Output: temp_new_weekly_report_18.cust_id
  • Group Key: temp_new_weekly_report_18.cust_id
  • Buffers: shared hit=1 read=3,905,247
17. 267,318.373 267,318.373 ↑ 1.0 28,527,397 1

Seq Scan on public.temp_new_weekly_report_18 (cost=0.00..4,190,589.12 rows=28,534,112 width=18) (actual time=0.016..267,318.373 rows=28,527,397 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=3,905,247
18. 3,879.470 47,870.168 ↓ 4.0 11,670,841 1

Hash (cost=589,224.30..589,224.30 rows=2,920,311 width=17) (actual time=47,870.168..47,870.168 rows=11,670,841 loops=1)

  • Output: crm.loyalty_card_no
  • Buckets: 16,777,216 (originally 4194304) Batches: 1 (originally 1) Memory Usage: 689,540kB
  • Buffers: shared hit=2 read=414,001
19. 43,988.318 43,990.698 ↓ 4.0 11,670,841 1

Seq Scan on public.t_crm_loyalty_card crm (cost=4.62..589,224.30 rows=2,920,311 width=17) (actual time=2.466..43,990.698 rows=11,670,841 loops=1)

  • Output: crm.loyalty_card_no
  • Filter: ((NOT (hashed SubPlan 5)) AND (NOT (hashed SubPlan 6)))
  • Rows Removed by Filter: 9,919
  • Buffers: shared hit=2 read=414,001
20.          

SubPlan (for Seq Scan)

21. 0.036 1.259 ↑ 1.0 67 1

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

  • Output: t_other_stores_1.store_id
  • Group Key: t_other_stores_1.store_id
  • Buffers: shared read=1
22. 1.223 1.223 ↑ 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=1.219..1.223 rows=67 loops=1)

  • Output: t_other_stores_1.store_id
  • Buffers: shared read=1
23. 0.020 1.121 ↑ 1.0 38 1

HashAggregate (cost=1.47..1.85 rows=38 width=5) (actual time=1.116..1.121 rows=38 loops=1)

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

Seq Scan on public.zudio_stores zudio_stores_1 (cost=0.00..1.38 rows=38 width=5) (actual time=1.099..1.101 rows=38 loops=1)

  • Output: zudio_stores_1.store_id
  • Buffers: shared read=1
25. 2,890,874.032 2,890,874.096 ↓ 3.5 7 4,708,264

Index Scan using idx_card_no_t_pos_invoice on public.t_pos_invoice pos (cost=5.19..7.20 rows=2 width=39) (actual time=0.080..0.614 rows=7 loops=4,708,264)

  • 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=30,722,499 read=24,574,960 written=88,658
26.          

SubPlan (for Index Scan)

27. 0.028 0.043 ↑ 1.0 67 1

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

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

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

  • Output: t_other_stores.store_id
  • Buffers: shared hit=1
29. 0.013 0.021 ↑ 1.0 38 1

HashAggregate (cost=1.47..1.85 rows=38 width=5) (actual time=0.018..0.021 rows=38 loops=1)

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

Seq Scan on public.zudio_stores (cost=0.00..1.38 rows=38 width=5) (actual time=0.006..0.008 rows=38 loops=1)

  • Output: zudio_stores.store_id
  • Buffers: shared hit=1
31. 9,707,848.593 9,707,949.680 ↑ 4.0 4 32,908,304

Index Scan using idx_invoice_no_t_pos_invoice_item on public.t_pos_invoice_item item (cost=8.55..19.16 rows=16 width=24) (actual time=0.136..0.295 rows=4 loops=32,908,304)

  • 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=153,469,863 read=92,768,114 written=324,705
32.          

SubPlan (for Index Scan)

33. 101.087 101.087 ↑ 1.0 398 1

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

  • Output: t_cat_not_used.category_id
  • Buffers: shared read=3