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=414670.42..2591104.83 rows=200 width=25) (actual time=.. rows= loops=)

2. 0.000 0.000 ↓ 0.0

Append (cost=414670.42..2591100.81 rows=1608 width=25) (actual time=.. rows= loops=)

3. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=414670.42..1279016.68 rows=200 width=118) (actual time=.. rows= loops=)

4. 0.000 0.000 ↓ 0.0

Subquery Scan on m (cost=414670.42..1279014.68 rows=200 width=118) (actual time=.. rows= loops=)

5. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=414670.42..1279012.68 rows=200 width=118) (actual time=.. rows= loops=)

6. 0.000 0.000 ↓ 0.0

Append (cost=414670.42..1279002.63 rows=4020 width=118) (actual time=.. rows= loops=)

7. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=414670.42..638517.81 rows=2010 width=118) (actual time=.. rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=414670.42..638497.71 rows=2010 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=200755.48..424499.85 rows=4020 width=118) (actual time=.. rows= loops=)

10. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=200755.48..424459.65 rows=4020 width=16) (actual time=.. rows= loops=)

11. 0.000 0.000 ↓ 0.0

Append (cost=200755.48..420419.30 rows=808071 width=16) (actual time=.. rows= loops=)

12. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=200755.48..212916.75 rows=4020 width=16) (actual time=.. rows= loops=)

13. 0.000 0.000 ↓ 0.0

Merge Join (cost=200755.48..212876.55 rows=4020 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=144552.06..146562.19 rows=804051 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..51942.51 rows=804051 width=12) (actual time=.. rows= loops=)

16. 0.000 0.000 ↓ 0.0

Sort (cost=56203.42..56213.47 rows=4020 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..55962.76 rows=4020 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=165241.51..207502.55 rows=804051 width=16) (actual time=.. rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Join (cost=165241.51..199462.04 rows=804051 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=144552.06..146562.19 rows=804051 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..51942.51 rows=804051 width=12) (actual time=.. rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=12698.23..12698.23 rows=459698 width=12) (actual time=.. rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on twb_customize_idx_50006 (cost=0.00..12698.23 rows=459698 width=12) (actual time=.. rows= loops=)

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

Hash (cost=209990.10..209990.10 rows=313987 width=12) (actual time=.. rows= loops=)

25. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=196321.55..206850.23 rows=313987 width=17) (actual time=.. rows= loops=)

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

Sort (cost=196321.55..198522.83 rows=880513 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..91319.76 rows=880513 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=416637.44..640484.82 rows=2010 width=118) (actual time=.. rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=416637.44..640464.72 rows=2010 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=200755.48..424499.85 rows=4020 width=118) (actual time=.. rows= loops=)

31. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=200755.48..424459.65 rows=4020 width=16) (actual time=.. rows= loops=)

32. 0.000 0.000 ↓ 0.0

Append (cost=200755.48..420419.30 rows=808071 width=16) (actual time=.. rows= loops=)

33. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=200755.48..212916.75 rows=4020 width=16) (actual time=.. rows= loops=)

34. 0.000 0.000 ↓ 0.0

Merge Join (cost=200755.48..212876.55 rows=4020 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=144552.06..146562.19 rows=804051 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..51942.51 rows=804051 width=12) (actual time=.. rows= loops=)

37. 0.000 0.000 ↓ 0.0

Sort (cost=56203.42..56213.47 rows=4020 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..55962.76 rows=4020 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=165241.51..207502.55 rows=804051 width=16) (actual time=.. rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash Join (cost=165241.51..199462.04 rows=804051 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=144552.06..146562.19 rows=804051 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..51942.51 rows=804051 width=12) (actual time=.. rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash (cost=12698.23..12698.23 rows=459698 width=12) (actual time=.. rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on twb_customize_idx_50006 (cost=0.00..12698.23 rows=459698 width=12) (actual time=.. rows= loops=)

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

Hash (cost=211786.72..211786.72 rows=327619 width=12) (actual time=.. rows= loops=)

46. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=194408.84..208510.53 rows=327619 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=194408.84..196705.69 rows=918740 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..84566.88 rows=918740 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=1312055.97..1312084.13 rows=1408 width=12) (actual time=.. rows= loops=)

50. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1312055.97..1312070.05 rows=1408 width=12) (actual time=.. rows= loops=)

51. 0.000 0.000 ↓ 0.0

Hash Join (cost=414946.71..1312052.45 rows=1408 width=12) (actual time=.. rows= loops=)

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

Nested Loop (cost=414670.42..1311732.72 rows=7831 width=19) (actual time=.. rows= loops=)

53. 0.000 0.000 ↓ 0.0

Subquery Scan on d (cost=414670.42..1279014.68 rows=200 width=118) (actual time=.. rows= loops=)

54. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=414670.42..1279012.68 rows=200 width=118) (actual time=.. rows= loops=)

55. 0.000 0.000 ↓ 0.0

Append (cost=414670.42..1279002.63 rows=4020 width=118) (actual time=.. rows= loops=)

56. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=414670.42..638517.81 rows=2010 width=118) (actual time=.. rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=414670.42..638497.71 rows=2010 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=200755.48..424499.85 rows=4020 width=118) (actual time=.. rows= loops=)

59. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=200755.48..424459.65 rows=4020 width=16) (actual time=.. rows= loops=)

60. 0.000 0.000 ↓ 0.0

Append (cost=200755.48..420419.30 rows=808071 width=16) (actual time=.. rows= loops=)

61. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=200755.48..212916.75 rows=4020 width=16) (actual time=.. rows= loops=)

62. 0.000 0.000 ↓ 0.0

Merge Join (cost=200755.48..212876.55 rows=4020 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=144552.06..146562.19 rows=804051 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..51942.51 rows=804051 width=12) (actual time=.. rows= loops=)

65. 0.000 0.000 ↓ 0.0

Sort (cost=56203.42..56213.47 rows=4020 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..55962.76 rows=4020 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=165241.51..207502.55 rows=804051 width=16) (actual time=.. rows= loops=)

68. 0.000 0.000 ↓ 0.0

Hash Join (cost=165241.51..199462.04 rows=804051 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=144552.06..146562.19 rows=804051 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..51942.51 rows=804051 width=12) (actual time=.. rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash (cost=12698.23..12698.23 rows=459698 width=12) (actual time=.. rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on twb_customize_idx_50006 (cost=0.00..12698.23 rows=459698 width=12) (actual time=.. rows= loops=)

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

Hash (cost=209990.10..209990.10 rows=313987 width=12) (actual time=.. rows= loops=)

74. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=196321.55..206850.23 rows=313987 width=17) (actual time=.. rows= loops=)

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

Sort (cost=196321.55..198522.83 rows=880513 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..91319.76 rows=880513 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=416637.44..640484.82 rows=2010 width=118) (actual time=.. rows= loops=)

78. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=416637.44..640464.72 rows=2010 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=200755.48..424499.85 rows=4020 width=118) (actual time=.. rows= loops=)

80. 0.000 0.000 ↓ 0.0

HashSetOp Intersect (cost=200755.48..424459.65 rows=4020 width=16) (actual time=.. rows= loops=)

81. 0.000 0.000 ↓ 0.0

Append (cost=200755.48..420419.30 rows=808071 width=16) (actual time=.. rows= loops=)

82. 0.000 0.000 ↓ 0.0

Subquery Scan on "*SELECT* 1" (cost=200755.48..212916.75 rows=4020 width=16) (actual time=.. rows= loops=)

83. 0.000 0.000 ↓ 0.0

Merge Join (cost=200755.48..212876.55 rows=4020 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=144552.06..146562.19 rows=804051 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..51942.51 rows=804051 width=12) (actual time=.. rows= loops=)

86. 0.000 0.000 ↓ 0.0

Sort (cost=56203.42..56213.47 rows=4020 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..55962.76 rows=4020 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=165241.51..207502.55 rows=804051 width=16) (actual time=.. rows= loops=)

89. 0.000 0.000 ↓ 0.0

Hash Join (cost=165241.51..199462.04 rows=804051 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=144552.06..146562.19 rows=804051 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..51942.51 rows=804051 width=12) (actual time=.. rows= loops=)

92. 0.000 0.000 ↓ 0.0

Hash (cost=12698.23..12698.23 rows=459698 width=12) (actual time=.. rows= loops=)

93. 0.000 0.000 ↓ 0.0

Seq Scan on twb_customize_idx_50006 (cost=0.00..12698.23 rows=459698 width=12) (actual time=.. rows= loops=)

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

Hash (cost=211786.72..211786.72 rows=327619 width=12) (actual time=.. rows= loops=)

95. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=194408.84..208510.53 rows=327619 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=194408.84..196705.69 rows=918740 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..84566.88 rows=918740 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=1286 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=1286 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))