explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ESmx

Settings
# exclusive inclusive rows x rows loops node
1. 6.714 22,334.623 ↓ 12.7 4,850 1

HashAggregate (cost=3,662,341.43..3,662,345.25 rows=382 width=596) (actual time=22,332.844..22,334.623 rows=4,850 loops=1)

  • Group Key: a_mail.mail_id_, (filial_name(a_mail.current_department_)), (filial_name(filial_id(a_mail.current_department_))), (date(a_mail.modify_date_)), ((CURRENT_DATE - date(a_mail.modify_date_))), (((CASE WHEN (((CURRENT_DATE - date(a_mail.modify_date_)) - 14) < 0) THEN 0 ELSE ((CURRENT_DATE - date(a_mail.modify_date_)) - 14) END))::bigint)
2. 0.468 22,327.909 ↓ 12.7 4,850 1

Append (cost=2,196,534.20..3,662,335.70 rows=382 width=596) (actual time=14,986.500..22,327.909 rows=4,850 loops=1)

3. 5.464 14,987.674 ↓ 9.3 2,661 1

HashAggregate (cost=2,196,534.20..2,196,537.77 rows=286 width=596) (actual time=14,986.498..14,987.674 rows=2,661 loops=1)

  • Group Key: a_mail.mail_id_, (filial_name(a_mail.current_department_)), (filial_name(filial_id(a_mail.current_department_))), (date(a_mail.modify_date_)), ((CURRENT_DATE - date(a_mail.modify_date_))), (CASE WHEN (((CURRENT_DATE - date(a_mail.modify_date_)) - 14) < 0) THEN 0 ELSE ((CURRENT_DATE - date(a_mail.modify_date_)) - 14) END)
4. 0.601 14,982.210 ↓ 9.3 2,661 1

Append (cost=0.56..2,196,529.91 rows=286 width=592) (actual time=3,479.996..14,982.210 rows=2,661 loops=1)

5. 8,033.179 8,033.179 ↓ 10.7 512 1

Index Scan using a_mail_mail_status_ on a_mail (cost=0.56..732,138.64 rows=48 width=90) (actual time=3,479.994..8,033.179 rows=512 loops=1)

  • Index Cond: ((mail_status_)::text = 'Delivered'::text)
  • Filter: (((mail_id_)::text ~ '^(?:(?:IQ|KW).*KZ)$'::text) AND (date_trunc('day'::text, modify_date_) > '2019-01-01 00:00:00'::timestamp without time zone) AND (date_trunc('day'::text, modify_date_) <= (CURRENT_DATE - 15)))
  • Rows Removed by Filter: 945858
6. 3,530.322 3,530.322 ↓ 14.4 1,370 1

Index Scan using a_mail_mail_status_ on a_mail a_mail_1 (cost=0.56..732,175.42 rows=95 width=90) (actual time=1,523.762..3,530.322 rows=1,370 loops=1)

  • Index Cond: ((mail_status_)::text = 'Delivered'::text)
  • Filter: (((mail_id_)::text ~ '^(?:(?:KD|WW|WL).*KZ)$'::text) AND (date_trunc('day'::text, modify_date_) > '2019-01-01 00:00:00'::timestamp without time zone) AND (date_trunc('day'::text, modify_date_) <= (CURRENT_DATE - 31)))
  • Rows Removed by Filter: 945000
7. 3,418.108 3,418.108 ↓ 5.4 779 1

Index Scan using a_mail_mail_status_ on a_mail a_mail_2 (cost=0.56..732,212.98 rows=143 width=90) (actual time=825.716..3,418.108 rows=779 loops=1)

  • Index Cond: ((mail_status_)::text = 'Delivered'::text)
  • Filter: (((mail_id_)::text ~ '^(?:(?:AV).*KZ)$'::text) AND (date_trunc('day'::text, modify_date_) > '2019-01-01 00:00:00'::timestamp without time zone) AND (date_trunc('day'::text, modify_date_) <= (CURRENT_DATE - 8)))
  • Rows Removed by Filter: 945591
8. 3,382.277 3,382.277 ↓ 0.0 0 1

Index Scan using a_mail_mail_status_ on a_mail a_mail_3 (cost=0.56..732,117.70 rows=1 width=94) (actual time=3,382.276..3,382.277 rows=0 loops=1)

  • Index Cond: ((mail_status_)::text = 'Delivered'::text)
  • Filter: (((mail_id_)::text ~ '^(?:(?:WV).*KZ)$'::text) AND (date_trunc('day'::text, modify_date_) > '2019-01-01 00:00:00'::timestamp without time zone) AND (date_trunc('day'::text, modify_date_) <= (CURRENT_DATE - 15)))
  • Rows Removed by Filter: 946370
9.          

SubPlan (for Index Scan)

10. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=15.84..15.85 rows=1 width=8) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Function Scan on generate_series the_day_1 (cost=0.01..15.01 rows=333 width=0) (never executed)

  • Filter: (date_part('ISODOW'::text, the_day) < '6'::double precision)
12. 3,907.143 3,957.490 ↓ 23.0 2,189 1

Index Scan using a_mail_mail_status_ on a_mail a_mail_4 (cost=0.56..733,679.27 rows=95 width=94) (actual time=948.639..3,957.490 rows=2,189 loops=1)

  • Index Cond: ((mail_status_)::text = 'Delivered'::text)
  • Filter: (((mail_id_)::text ~ '^(?:(?:VZ|VC|VD|VB|VA|VE|VF|VH|VL|VM|VP|VN|VI|VY|VT|VX).*KZ)$'::text) AND (date_trunc('day'::text, modify_date_) > '2019-01-01 00:00:00'::timestamp without time zone) AND (date_trunc('day'::text, modify_date_) <= (CURRENT_DATE - 8)))
  • Rows Removed by Filter: 944181
13.          

SubPlan (for Index Scan)

14. 6.567 50.347 ↑ 1.0 1 2,189

Aggregate (cost=15.84..15.85 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=2,189)

15. 43.780 43.780 ↑ 8.3 40 2,189

Function Scan on generate_series the_day (cost=0.01..15.01 rows=333 width=0) (actual time=0.008..0.020 rows=40 loops=2,189)

  • Filter: (date_part('ISODOW'::text, the_day) < '6'::double precision)
  • Rows Removed by Filter: 15
Planning time : 9.605 ms
Execution time : 22,335.499 ms