explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XP9E

Settings
# exclusive inclusive rows x rows loops node
1. 5.765 9,940.879 ↓ 1.2 596 1

Hash Right Join (cost=316,311.41..318,636.31 rows=488 width=382) (actual time=9,816.695..9,940.879 rows=596 loops=1)

  • Hash Cond: (sale.partner_id = customer.customer_id)
  • Buffers: shared hit=114454 read=448
2.          

CTE item

3. 992.796 7,884.601 ↓ 3,011.2 602,237 1

GroupAggregate (cost=219,833.83..241,640.48 rows=200 width=36) (actual time=6,628.369..7,884.601 rows=602,237 loops=1)

  • Group Key: _cx_customer_product_gender_sale_report.partner_id
  • Buffers: shared hit=38358
4. 1,393.842 6,891.805 ↓ 2.4 1,767,881 1

Sort (cost=219,833.83..221,650.85 rows=726,805 width=68) (actual time=6,628.299..6,891.805 rows=1,767,881 loops=1)

  • Sort Key: _cx_customer_product_gender_sale_report.partner_id, (sum((_cx_customer_product_gender_sale_report.sold_item - _cx_customer_product_gender_sale_report.returned_item))) DESC
  • Sort Method: quicksort Memory: 152038kB
  • Buffers: shared hit=38358
5. 4,230.062 5,497.963 ↓ 2.4 1,767,881 1

HashAggregate (cost=139,989.91..149,074.98 rows=726,805 width=68) (actual time=4,467.904..5,497.963 rows=1,767,881 loops=1)

  • Group Key: _cx_customer_product_gender_sale_report.partner_id, CASE WHEN (_cx_customer_product_gender_sale_report.gender_name IS NULL) THEN 'Khác'::character varying ELSE _cx_customer_product_gender_sale_report.gender_name END
  • Buffers: shared hit=38355
6. 1,267.901 1,267.901 ↑ 1.0 3,356,674 1

Index Scan using idx_cx_customer_product_gender_sale_report_order_date on _cx_customer_product_gender_sale_report (cost=0.43..106,250.75 rows=3,373,916 width=43) (actual time=0.028..1,267.901 rows=3,356,674 loops=1)

  • Index Cond: ((order_date >= '2019-01-01'::date) AND (order_date <= '2019-12-31'::date))
  • Filter: (partner_id IS NOT NULL)
  • Buffers: shared hit=38355
7.          

CTE customer

8. 0.458 742.439 ↓ 596.0 596 1

Nested Loop Left Join (cost=2,194.94..2,202.87 rows=1 width=234) (actual time=514.359..742.439 rows=596 loops=1)

  • Buffers: shared hit=56908 read=448
9. 0.801 685.957 ↓ 596.0 596 1

Nested Loop Left Join (cost=2,194.51..2,201.98 rows=1 width=223) (actual time=513.968..685.957 rows=596 loops=1)

  • Buffers: shared hit=54648 read=321
10. 0.861 683.964 ↓ 596.0 596 1

Nested Loop Left Join (cost=2,194.51..2,201.95 rows=1 width=195) (actual time=513.929..683.964 rows=596 loops=1)

  • Buffers: shared hit=53455 read=321
11. 1.725 554.963 ↓ 596.0 596 1

Nested Loop (cost=2,194.09..2,200.58 rows=1 width=152) (actual time=513.502..554.963 rows=596 loops=1)

  • Buffers: shared hit=51386
12. 1.860 515.519 ↓ 3,429.0 3,429 1

Hash Join (cost=2,193.66..2,198.10 rows=1 width=126) (actual time=513.075..515.519 rows=3,429 loops=1)

  • Hash Cond: ((cus_group.partner_id = cus_group_1.partner_id) AND (cus_group.group_number = (min(cus_group_1.group_number))))
  • Buffers: shared hit=34982
13.          

CTE cus_group

14. 1.032 509.921 ↓ 19.5 3,429 1

Unique (cost=2,181.78..2,183.10 rows=176 width=20) (actual time=508.363..509.921 rows=3,429 loops=1)

  • Buffers: shared hit=34982
15.          

Initplan (for Unique)

16. 0.079 0.079 ↑ 1.0 1 1

Index Only Scan using idx_date_series on date_series (cost=0.28..2.30 rows=1 width=4) (actual time=0.078..0.079 rows=1 loops=1)

  • Index Cond: (date_of_year = '2019-06-01 00:00:00'::timestamp without time zone)
  • Heap Fetches: 1
  • Buffers: shared hit=6
17. 4.224 508.810 ↓ 44.4 7,808 1

Sort (cost=2,179.48..2,179.92 rows=176 width=20) (actual time=508.361..508.810 rows=7,808 loops=1)

  • Sort Key: customer_segmentation.partner_id, (("left"((customer_segmentation.behavior_status)::text, 1))::integer)
  • Sort Method: quicksort Memory: 803kB
  • Buffers: shared hit=34982
18. 504.586 504.586 ↓ 44.4 7,808 1

Index Scan using idx_customer_segmentation_behavior on customer_segmentation (cost=0.56..2,172.92 rows=176 width=20) (actual time=0.162..504.586 rows=7,808 loops=1)

  • Index Cond: ((order_month >= $1) AND (order_month <= '2019-12-31'::date) AND ((behavior_status)::text = '1.Most Intensive'::text))
  • Buffers: shared hit=34979
19. 508.971 508.971 ↓ 19.5 3,429 1

CTE Scan on cus_group (cost=0.00..3.52 rows=176 width=126) (actual time=508.366..508.971 rows=3,429 loops=1)

  • Buffers: shared hit=34982
20. 0.567 4.688 ↓ 19.5 3,429 1

Hash (cost=7.92..7.92 rows=176 width=8) (actual time=4.688..4.688 rows=3,429 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 166kB
21. 1.843 4.121 ↓ 19.5 3,429 1

HashAggregate (cost=4.40..6.16 rows=176 width=8) (actual time=3.621..4.121 rows=3,429 loops=1)

  • Group Key: cus_group_1.partner_id
22. 2.278 2.278 ↓ 19.5 3,429 1

CTE Scan on cus_group cus_group_1 (cost=0.00..3.52 rows=176 width=8) (actual time=0.001..2.278 rows=3,429 loops=1)

23. 37.719 37.719 ↓ 0.0 0 3,429

Index Scan using idx_loyalty_card_datamart_customer_id on _loyalty_card_datamart card (cost=0.43..2.46 rows=1 width=34) (actual time=0.011..0.011 rows=0 loops=3,429)

  • Index Cond: (customer_id = cus_group.partner_id)
  • Filter: ((last_active_date >= '2019-07-01'::date) AND (last_active_date <= '2019-12-01'::date) AND ((card_type_name)::text = 'Gold Card'::text) AND ((state)::text = 'activated'::text))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=16404
24. 128.140 128.140 ↑ 1.0 1 596

Index Scan using retail__res_partner_id_index on retail__res_partner partner (cost=0.43..1.36 rows=1 width=47) (actual time=0.215..0.215 rows=1 loops=596)

  • Index Cond: (id = card.customer_id)
  • Buffers: shared hit=2069 read=321
25. 1.192 1.192 ↑ 1.0 1 596

Index Scan using idx_retail__loyalty_zone_id on retail__loyalty_zone lzone (cost=0.00..0.02 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=596)

  • Index Cond: (id = card.zone_id)
  • Buffers: shared hit=1193
26. 56.024 56.024 ↑ 1.0 1 596

Index Scan using idx_mv_list_phone_customer_id on mv_list_phone_customer phone (cost=0.43..0.88 rows=1 width=15) (actual time=0.093..0.094 rows=1 loops=596)

  • Index Cond: (customer_id = card.customer_id)
  • Buffers: shared hit=2260 read=127
27.          

CTE sale

28. 632.913 1,002.166 ↑ 2.0 48,796 1

HashAggregate (cost=66,846.39..71,486.41 rows=97,685 width=12) (actual time=895.393..1,002.166 rows=48,796 loops=1)

  • Group Key: _cx_customer_store_sale_report.partner_id
  • Filter: (round(sum((_cx_customer_store_sale_report.gross_revenue - _cx_customer_store_sale_report.discount_value))) >= '5000000'::double precision)
  • Rows Removed by Filter: 553441
  • Buffers: shared hit=19188
29. 369.253 369.253 ↑ 1.0 1,461,922 1

Index Scan using idx_cx_customer_store_sale_report_order_date on _cx_customer_store_sale_report (cost=0.43..48,540.25 rows=1,464,491 width=20) (actual time=0.058..369.253 rows=1,461,922 loops=1)

  • Index Cond: ((order_date >= '2019-01-01'::date) AND (order_date <= '2019-12-31'::date))
  • Filter: (partner_id IS NOT NULL)
  • Buffers: shared hit=19188
30. 1,013.968 1,013.968 ↑ 2.0 48,796 1

CTE Scan on sale (cost=0.00..1,953.70 rows=97,685 width=12) (actual time=895.400..1,013.968 rows=48,796 loops=1)

  • Buffers: shared hit=19188
31. 0.488 8,921.146 ↓ 596.0 596 1

Hash (cost=4.79..4.79 rows=1 width=370) (actual time=8,921.146..8,921.146 rows=596 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 162kB
  • Buffers: shared hit=95266 read=448
32. 75.187 8,920.658 ↓ 596.0 596 1

Hash Right Join (cost=0.03..4.79 rows=1 width=370) (actual time=7,372.502..8,920.658 rows=596 loops=1)

  • Hash Cond: (item.partner_id = customer.customer_id)
  • Buffers: shared hit=95266 read=448
33. 8,101.723 8,101.723 ↓ 3,011.2 602,237 1

CTE Scan on item (cost=0.00..4.00 rows=200 width=36) (actual time=6,628.378..8,101.723 rows=602,237 loops=1)

  • Buffers: shared hit=38358
34. 0.503 743.748 ↓ 596.0 596 1

Hash (cost=0.02..0.02 rows=1 width=334) (actual time=743.748..743.748 rows=596 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 98kB
  • Buffers: shared hit=56908 read=448
35. 743.245 743.245 ↓ 596.0 596 1

CTE Scan on customer (cost=0.00..0.02 rows=1 width=334) (actual time=514.362..743.245 rows=596 loops=1)

  • Buffers: shared hit=56908 read=448
Planning time : 5.807 ms
Execution time : 10,007.035 ms