explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 4Z7

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

HashSetOp Except (cost=414,670.42..2,591,104.83 rows=200 width=25) (actual time=.. rows= loops=)

2. 0.000 0.000 ↓ 0.0

Append (cost=414,670.42..2,591,100.81 rows=1,608 width=25) (actual time=.. rows= loops=)

3. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=414,670.42..1,279,016.68 rows=200 width=118) (actual time=.. rows= loops=)

4. 0.000 0.000 ↓ 0.0

Subquery Scan on m (cost=414,670.42..1,279,014.68 rows=200 width=118) (actual time=.. rows= loops=)

5. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=414,670.42..1,279,012.68 rows=200 width=118) (actual time=.. rows= loops=)

6. 0.000 0.000 ↓ 0.0

Append (cost=414,670.42..1,279,002.63 rows=4,020 width=118) (actual time=.. rows= loops=)

7. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=414,670.42..638,517.81 rows=2,010 width=118) (actual time=.. rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=414,670.42..638,497.71 rows=2,010 width=118) (actual time=.. rows= loops=)

  • Hash Cond: ((a.user_id)::text = (i.customerno)::text)
9. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=200,755.48..424,499.85 rows=4,020 width=118) (actual time=.. rows= loops=)

10. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=200,755.48..424,459.65 rows=4,020 width=16) (actual time=.. rows= loops=)

11. 0.000 0.000 ↓ 0.0

Append (cost=200,755.48..420,419.30 rows=808,071 width=16) (actual time=.. rows= loops=)

12. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=200,755.48..212,916.75 rows=4,020 width=16) (actual time=.. rows= loops=)

13. 0.000 0.000 ↓ 0.0

Merge Join (cost=200,755.48..212,876.55 rows=4,020 width=16) (actual time=.. rows= loops=)

  • Merge Cond: ((public.retail_tb_customer.customerno)::text = (public.retail_tb_customer.customerno)::text)
14. 0.000 0.000 ↓ 0.0

Sort (cost=144,552.06..146,562.19 rows=804,051 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
15. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..51,942.51 rows=804,051 width=12) (actual time=.. rows= loops=)

16. 0.000 0.000 ↓ 0.0

Sort (cost=56,203.42..56,213.47 rows=4,020 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
17. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..55,962.76 rows=4,020 width=12) (actual time=.. rows= loops=)

  • Filter: (CASE WHEN ((mobile)::text ~ '^(1[3,4,5,8]){1}\d{9}$'::text) THEN '1'::text ELSE '0'::text END = '1'::text)
18. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 2" (cost=165,241.51..207,502.55 rows=804,051 width=16) (actual time=.. rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Join (cost=165,241.51..199,462.04 rows=804,051 width=16) (actual time=.. rows= loops=)

  • Hash Cond: ((public.retail_tb_customer.customerno)::text = (twb_customize_idx_50006.user_id)::text)
20. 0.000 0.000 ↓ 0.0

Sort (cost=144,552.06..146,562.19 rows=804,051 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
21. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..51,942.51 rows=804,051 width=12) (actual time=.. rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=12,698.23..12,698.23 rows=459,698 width=12) (actual time=.. rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on twb_customize_idx_50006 (cost=0.00..12,698.23 rows=459,698 width=12) (actual time=.. rows= loops=)

  • Filter: ((custidx_50006)::text = '13个包邮地区'::text)
24. 0.000 0.000 ↓ 0.0

Hash (cost=209,990.10..209,990.10 rows=313,987 width=12) (actual time=.. rows= loops=)

25. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=196,321.55..206,850.23 rows=313,987 width=17) (actual time=.. rows= loops=)

  • Filter: (sum(i.payment) > 0::numeric)
26. 0.000 0.000 ↓ 0.0

Sort (cost=196,321.55..198,522.83 rows=880,513 width=17) (actual time=.. rows= loops=)

  • Sort Key: i.customerno
27. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_order i (cost=0.00..91,319.76 rows=880,513 width=17) (actual time=.. rows= loops=)

  • Filter: ((created >= '2008-07-01 00:00:00'::timestamp without time zone) AND (created <= '2012-05-01 23:59:59'::timestamp without time zone) AND ((dp_id)::text = '59083
28. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 2" (cost=416,637.44..640,484.82 rows=2,010 width=118) (actual time=.. rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=416,637.44..640,464.72 rows=2,010 width=118) (actual time=.. rows= loops=)

  • Hash Cond: ((a.user_id)::text = (i.customerno)::text)
30. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=200,755.48..424,499.85 rows=4,020 width=118) (actual time=.. rows= loops=)

31. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=200,755.48..424,459.65 rows=4,020 width=16) (actual time=.. rows= loops=)

32. 0.000 0.000 ↓ 0.0

Append (cost=200,755.48..420,419.30 rows=808,071 width=16) (actual time=.. rows= loops=)

33. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=200,755.48..212,916.75 rows=4,020 width=16) (actual time=.. rows= loops=)

34. 0.000 0.000 ↓ 0.0

Merge Join (cost=200,755.48..212,876.55 rows=4,020 width=16) (actual time=.. rows= loops=)

  • Merge Cond: ((public.retail_tb_customer.customerno)::text = (public.retail_tb_customer.customerno)::text)
35. 0.000 0.000 ↓ 0.0

Sort (cost=144,552.06..146,562.19 rows=804,051 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
36. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..51,942.51 rows=804,051 width=12) (actual time=.. rows= loops=)

37. 0.000 0.000 ↓ 0.0

Sort (cost=56,203.42..56,213.47 rows=4,020 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
38. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..55,962.76 rows=4,020 width=12) (actual time=.. rows= loops=)

  • Filter: (CASE WHEN ((mobile)::text ~ '^(1[3,4,5,8]){1}\d{9}$'::text) THEN '1'::text ELSE '0'::text END = '1'::text)
39. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 2" (cost=165,241.51..207,502.55 rows=804,051 width=16) (actual time=.. rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash Join (cost=165,241.51..199,462.04 rows=804,051 width=16) (actual time=.. rows= loops=)

  • Hash Cond: ((public.retail_tb_customer.customerno)::text = (twb_customize_idx_50006.user_id)::text)
41. 0.000 0.000 ↓ 0.0

Sort (cost=144,552.06..146,562.19 rows=804,051 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
42. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..51,942.51 rows=804,051 width=12) (actual time=.. rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash (cost=12,698.23..12,698.23 rows=459,698 width=12) (actual time=.. rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on twb_customize_idx_50006 (cost=0.00..12,698.23 rows=459,698 width=12) (actual time=.. rows= loops=)

  • Filter: ((custidx_50006)::text = '13个包邮地区'::text)
45. 0.000 0.000 ↓ 0.0

Hash (cost=211,786.72..211,786.72 rows=327,619 width=12) (actual time=.. rows= loops=)

46. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=194,408.84..208,510.53 rows=327,619 width=20) (actual time=.. rows= loops=)

  • Filter: (max((i.created)::date) >= to_date('2011-11-10'::text, 'yyyy-mm-dd'::text))
47. 0.000 0.000 ↓ 0.0

Sort (cost=194,408.84..196,705.69 rows=918,740 width=20) (actual time=.. rows= loops=)

  • Sort Key: i.customerno
48. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_order i (cost=0.00..84,566.88 rows=918,740 width=20) (actual time=.. rows= loops=)

  • Filter: (((dp_id)::text = '59083927'::text) AND (ccms_order_status = ANY ('{21,22,23}'::smallint[])))
49. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 2" (cost=1,312,055.97..1,312,084.13 rows=1,408 width=12) (actual time=.. rows= loops=)

50. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,312,055.97..1,312,070.05 rows=1,408 width=12) (actual time=.. rows= loops=)

51. 0.000 0.000 ↓ 0.0

Hash Join (cost=414,946.71..1,312,052.45 rows=1,408 width=12) (actual time=.. rows= loops=)

  • Hash Cond: (a.subjob_id = c.subjob_id)
52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=414,670.42..1,311,732.72 rows=7,831 width=19) (actual time=.. rows= loops=)

53. 0.000 0.000 ↓ 0.0

Subquery Scan on d (cost=414,670.42..1,279,014.68 rows=200 width=118) (actual time=.. rows= loops=)

54. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=414,670.42..1,279,012.68 rows=200 width=118) (actual time=.. rows= loops=)

55. 0.000 0.000 ↓ 0.0

Append (cost=414,670.42..1,279,002.63 rows=4,020 width=118) (actual time=.. rows= loops=)

56. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=414,670.42..638,517.81 rows=2,010 width=118) (actual time=.. rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=414,670.42..638,497.71 rows=2,010 width=118) (actual time=.. rows= loops=)

  • Hash Cond: ((a.user_id)::text = (i.customerno)::text)
58. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=200,755.48..424,499.85 rows=4,020 width=118) (actual time=.. rows= loops=)

59. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=200,755.48..424,459.65 rows=4,020 width=16) (actual time=.. rows= loops=)

60. 0.000 0.000 ↓ 0.0

Append (cost=200,755.48..420,419.30 rows=808,071 width=16) (actual time=.. rows= loops=)

61. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=200,755.48..212,916.75 rows=4,020 width=16) (actual time=.. rows= loops=)

62. 0.000 0.000 ↓ 0.0

Merge Join (cost=200,755.48..212,876.55 rows=4,020 width=16) (actual time=.. rows= loops=)

  • Merge Cond: ((public.retail_tb_customer.customerno)::text = (public.retail_tb_customer.customerno)::text)
63. 0.000 0.000 ↓ 0.0

Sort (cost=144,552.06..146,562.19 rows=804,051 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
64. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..51,942.51 rows=804,051 width=12) (actual time=.. rows= loops=)

65. 0.000 0.000 ↓ 0.0

Sort (cost=56,203.42..56,213.47 rows=4,020 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
66. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..55,962.76 rows=4,020 width=12) (actual time=.. rows= loops=)

  • Filter: (CASE WHEN ((mobile)::text ~ '^(1[3,4,5,8]){1}\d{9}$'::text) THEN '1'::text ELSE '0'::text END = '1'::text)
67. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 2" (cost=165,241.51..207,502.55 rows=804,051 width=16) (actual time=.. rows= loops=)

68. 0.000 0.000 ↓ 0.0

Hash Join (cost=165,241.51..199,462.04 rows=804,051 width=16) (actual time=.. rows= loops=)

  • Hash Cond: ((public.retail_tb_customer.customerno)::text = (twb_customize_idx_50006.user_id)::text)
69. 0.000 0.000 ↓ 0.0

Sort (cost=144,552.06..146,562.19 rows=804,051 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
70. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..51,942.51 rows=804,051 width=12) (actual time=.. rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash (cost=12,698.23..12,698.23 rows=459,698 width=12) (actual time=.. rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on twb_customize_idx_50006 (cost=0.00..12,698.23 rows=459,698 width=12) (actual time=.. rows= loops=)

  • Filter: ((custidx_50006)::text = '13个包邮地区'::text)
73. 0.000 0.000 ↓ 0.0

Hash (cost=209,990.10..209,990.10 rows=313,987 width=12) (actual time=.. rows= loops=)

74. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=196,321.55..206,850.23 rows=313,987 width=17) (actual time=.. rows= loops=)

  • Filter: (sum(i.payment) > 0::numeric)
75. 0.000 0.000 ↓ 0.0

Sort (cost=196,321.55..198,522.83 rows=880,513 width=17) (actual time=.. rows= loops=)

  • Sort Key: i.customerno
76. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_order i (cost=0.00..91,319.76 rows=880,513 width=17) (actual time=.. rows= loops=)

  • Filter: ((created >= '2008-07-01 00:00:00'::timestamp without time zone) AND (created <= '2012-05-01 23:59:59'::timestamp without time zone) AND ((dp_
77. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 2" (cost=416,637.44..640,484.82 rows=2,010 width=118) (actual time=.. rows= loops=)

78. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=416,637.44..640,464.72 rows=2,010 width=118) (actual time=.. rows= loops=)

  • Hash Cond: ((a.user_id)::text = (i.customerno)::text)
79. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=200,755.48..424,499.85 rows=4,020 width=118) (actual time=.. rows= loops=)

80. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=200,755.48..424,459.65 rows=4,020 width=16) (actual time=.. rows= loops=)

81. 0.000 0.000 ↓ 0.0

Append (cost=200,755.48..420,419.30 rows=808,071 width=16) (actual time=.. rows= loops=)

82. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=200,755.48..212,916.75 rows=4,020 width=16) (actual time=.. rows= loops=)

83. 0.000 0.000 ↓ 0.0

Merge Join (cost=200,755.48..212,876.55 rows=4,020 width=16) (actual time=.. rows= loops=)

  • Merge Cond: ((public.retail_tb_customer.customerno)::text = (public.retail_tb_customer.customerno)::text)
84. 0.000 0.000 ↓ 0.0

Sort (cost=144,552.06..146,562.19 rows=804,051 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
85. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..51,942.51 rows=804,051 width=12) (actual time=.. rows= loops=)

86. 0.000 0.000 ↓ 0.0

Sort (cost=56,203.42..56,213.47 rows=4,020 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
87. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..55,962.76 rows=4,020 width=12) (actual time=.. rows= loops=)

  • Filter: (CASE WHEN ((mobile)::text ~ '^(1[3,4,5,8]){1}\d{9}$'::text) THEN '1'::text ELSE '0'::text END = '1'::text)
88. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 2" (cost=165,241.51..207,502.55 rows=804,051 width=16) (actual time=.. rows= loops=)

89. 0.000 0.000 ↓ 0.0

Hash Join (cost=165,241.51..199,462.04 rows=804,051 width=16) (actual time=.. rows= loops=)

  • Hash Cond: ((public.retail_tb_customer.customerno)::text = (twb_customize_idx_50006.user_id)::text)
90. 0.000 0.000 ↓ 0.0

Sort (cost=144,552.06..146,562.19 rows=804,051 width=12) (actual time=.. rows= loops=)

  • Sort Key: public.retail_tb_customer.customerno
91. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_customer (cost=0.00..51,942.51 rows=804,051 width=12) (actual time=.. rows= loops=)

92. 0.000 0.000 ↓ 0.0

Hash (cost=12,698.23..12,698.23 rows=459,698 width=12) (actual time=.. rows= loops=)

93. 0.000 0.000 ↓ 0.0

Seq Scan on twb_customize_idx_50006 (cost=0.00..12,698.23 rows=459,698 width=12) (actual time=.. rows= loops=)

  • Filter: ((custidx_50006)::text = '13个包邮地区'::text)
94. 0.000 0.000 ↓ 0.0

Hash (cost=211,786.72..211,786.72 rows=327,619 width=12) (actual time=.. rows= loops=)

95. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=194,408.84..208,510.53 rows=327,619 width=20) (actual time=.. rows= loops=)

  • Filter: (max((i.created)::date) >= to_date('2011-11-10'::text, 'yyyy-mm-dd'::text))
96. 0.000 0.000 ↓ 0.0

Sort (cost=194,408.84..196,705.69 rows=918,740 width=20) (actual time=.. rows= loops=)

  • Sort Key: i.customerno
97. 0.000 0.000 ↓ 0.0

Seq Scan on retail_tb_order i (cost=0.00..84,566.88 rows=918,740 width=20) (actual time=.. rows= loops=)

  • Filter: (((dp_id)::text = '59083927'::text) AND (ccms_order_status = ANY ('{21,22,23}'::smallint[])))
98. 0.000 0.000 ↓ 0.0

Index Scan using idx_twf_log_channel_user_user_id on twf_log_channel_user a (cost=0.00..163.10 rows=39 width=19) (actual time=.. rows= loops=)

  • Index Cond: ((user_id)::text = (d.user_id)::text)
99. 0.000 0.000 ↓ 0.0

Hash (cost=260.21..260.21 rows=1,286 width=6) (actual time=.. rows= loops=)

100. 0.000 0.000 ↓ 0.0

Seq Scan on twf_log_channel c (cost=0.00..260.21 rows=1,286 width=6) (actual time=.. rows= loops=)

  • Filter: ((plan_starttime >= '2012-04-01 00:00:00'::timestamp without time zone) AND (plan_endtime <= '2012-04-30 23:54:29'::timestamp without time zone) AND (channel_type = 1::numeric))