explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VepY

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 1,514.538 ↑ 69.2 13 1

Sort (cost=711,879.40..711,881.65 rows=899 width=32) (actual time=1,514.537..1,514.538 rows=13 loops=1)

  • Sort Key: (CASE WHEN (cte0.c2 IS NOT NULL) THEN cte0.c2 WHEN (cte1.c2 IS NOT NULL) THEN cte1.c2 WHEN (cte2.c2 IS NOT NULL) THEN cte2.c2 ELSE NULL::integer END) NULLS FIRST, (CASE WHEN (cte0.c3 IS NOT NULL) THEN cte0.c3 WHEN (cte1.c3 IS NOT NULL) THEN cte1.c3 WHEN (cte2.c3 IS NOT NULL) THEN cte2.c3 ELSE NULL::integer END) NULLS FIRST
  • Sort Method: quicksort Memory: 26kB
2.          

CTE meeting

3. 0.331 8.025 ↑ 20.1 13 1

GroupAggregate (cost=3,102.39..3,115.50 rows=261 width=16) (actual time=7.673..8.025 rows=13 loops=1)

  • Group Key: ((date_part('month'::text, timezone('US/Pacific'::text, to_timestamp(((t1662_meetingactivityaccountfact.c7794_activitydate / 1000))::double precision))))::integer), ((date_part('year'::text, timezone('US/Pacific'::text, to_timestamp(((t1662_meetingactivityaccountfact.c7794_activitydate / 1000))::double precision))))::integer)
4. 0.337 7.694 ↓ 3.6 972 1

Sort (cost=3,102.39..3,103.05 rows=267 width=12) (actual time=7.629..7.694 rows=972 loops=1)

  • Sort Key: ((date_part('month'::text, timezone('US/Pacific'::text, to_timestamp(((t1662_meetingactivityaccountfact.c7794_activitydate / 1000))::double precision))))::integer), ((date_part('year'::text, timezone('US/Pacific'::text, to_timestamp(((t1662_meetingactivityaccountfact.c7794_activitydate / 1000))::double precision))))::integer)
  • Sort Method: quicksort Memory: 70kB
5. 6.551 7.357 ↓ 3.6 972 1

Bitmap Heap Scan on t1662_meetingactivityaccountfact (cost=267.52..3,091.62 rows=267 width=12) (actual time=0.901..7.357 rows=972 loops=1)

  • Recheck Cond: ((c7812_accountid_accountownerid = 62) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1561642683951'::bigint) AND (c7794_activitydate < '1593178683951'::bigint) AND (c7807_activityownersfuserid = 62))
  • Rows Removed by Filter: 12,518
  • Heap Blocks: exact=465
6. 0.806 0.806 ↑ 1.0 13,913 1

Bitmap Index Scan on t1662_meetingactivityaccountfact_920_ptimestamp (cost=0.00..267.45 rows=14,271 width=0) (actual time=0.805..0.806 rows=13,913 loops=1)

  • Index Cond: (c7812_accountid_accountownerid = 62)
7.          

CTE email_sent

8. 0.913 490.983 ↑ 69.2 13 1

GroupAggregate (cost=354,239.80..354,284.80 rows=899 width=16) (actual time=489.961..490.983 rows=13 loops=1)

  • Group Key: ((date_part('month'::text, timezone('US/Pacific'::text, to_timestamp(((t1816_emailactivityaccountfact.c8036_activitydate / 1000))::double precision))))::integer), ((date_part('year'::text, timezone('US/Pacific'::text, to_timestamp(((t1816_emailactivityaccountfact.c8036_activitydate / 1000))::double precision))))::integer)
9. 0.000 490.070 ↓ 4.5 4,050 1

Sort (cost=354,239.80..354,242.06 rows=904 width=12) (actual time=489.868..490.070 rows=4,050 loops=1)

  • Sort Key: ((date_part('month'::text, timezone('US/Pacific'::text, to_timestamp(((t1816_emailactivityaccountfact.c8036_activitydate / 1000))::double precision))))::integer), ((date_part('year'::text, timezone('US/Pacific'::text, to_timestamp(((t1816_emailactivityaccountfact.c8036_activitydate / 1000))::double precision))))::integer)
  • Sort Method: quicksort Memory: 286kB
10. 45.273 491.539 ↓ 4.5 4,050 1

Gather (cost=43,322.54..354,195.42 rows=904 width=12) (actual time=160.763..491.539 rows=4,050 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 402.041 446.266 ↓ 3.6 1,350 3 / 3

Parallel Bitmap Heap Scan on t1816_emailactivityaccountfact (cost=42,322.54..353,089.21 rows=377 width=12) (actual time=117.444..446.266 rows=1,350 loops=3)

  • Recheck Cond: ((c8024_accountid_accountownerid = 62) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND c8043_isinternalinitiated AND c8040_isshared AND (c8036_activitydate >= '1561642683951'::bigint) AND (c8036_activitydate < '1593178683951'::bigint) AND (c8045_activityownersfuserid = 62))
  • Rows Removed by Filter: 744,692
  • Heap Blocks: exact=20,471
12. 44.225 44.225 ↑ 1.0 2,238,126 1 / 3

Bitmap Index Scan on t1816_emailactivityaccountfact_986_ptimestamp (cost=0.00..42,322.32 rows=2,291,451 width=0) (actual time=132.675..132.675 rows=2,238,126 loops=1)

  • Index Cond: (c8024_accountid_accountownerid = 62)
13.          

CTE email_received

14. 0.939 1,015.432 ↑ 55.7 13 1

GroupAggregate (cost=354,206.92..354,243.15 rows=724 width=16) (actual time=1,014.419..1,015.432 rows=13 loops=1)

  • Group Key: ((date_part('month'::text, timezone('US/Pacific'::text, to_timestamp(((t1816_emailactivityaccountfact_1.c8036_activitydate / 1000))::double precision))))::integer), ((date_part('year'::text, timezone('US/Pacific'::text, to_timestamp(((t1816_emailactivityaccountfact_1.c8036_activitydate / 1000))::double precision))))::integer)
15. 0.000 1,014.493 ↓ 4.0 2,918 1

Sort (cost=354,206.92..354,208.73 rows=727 width=12) (actual time=1,014.306..1,014.493 rows=2,918 loops=1)

  • Sort Key: ((date_part('month'::text, timezone('US/Pacific'::text, to_timestamp(((t1816_emailactivityaccountfact_1.c8036_activitydate / 1000))::double precision))))::integer), ((date_part('year'::text, timezone('US/Pacific'::text, to_timestamp(((t1816_emailactivityaccountfact_1.c8036_activitydate / 1000))::double precision))))::integer)
  • Sort Method: quicksort Memory: 233kB
16. 3.426 1,015.817 ↓ 4.0 2,918 1

Gather (cost=43,322.50..354,172.36 rows=727 width=12) (actual time=110.925..1,015.817 rows=2,918 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
17. 915.748 1,012.391 ↓ 9.6 2,918 1

Parallel Bitmap Heap Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=42,322.50..353,086.94 rows=303 width=12) (actual time=110.403..1,012.391 rows=2,918 loops=1)

  • Recheck Cond: ((c8024_accountid_accountownerid = 62) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (NOT c8043_isinternalinitiated) AND c8040_isshared AND (c8036_activitydate >= '1561642683951'::bigint) AND (c8036_activitydate < '1593178683951'::bigint) AND (c8045_activityownersfuserid = 62))
  • Rows Removed by Filter: 2,235,208
  • Heap Blocks: exact=62,121
18. 96.643 96.643 ↑ 1.0 2,238,126 1

Bitmap Index Scan on t1816_emailactivityaccountfact_986_ptimestamp (cost=0.00..42,322.32 rows=2,291,451 width=0) (actual time=96.643..96.643 rows=2,238,126 loops=1)

  • Index Cond: (c8024_accountid_accountownerid = 62)
19. 0.015 1,514.527 ↑ 69.2 13 1

Merge Full Join (cost=179.52..191.85 rows=899 width=32) (actual time=1,514.516..1,514.527 rows=13 loops=1)

  • Merge Cond: (((CASE WHEN (cte0.c2 IS NOT NULL) THEN cte0.c2 WHEN (cte1.c2 IS NOT NULL) THEN cte1.c2 ELSE NULL::integer END) = cte2.c2) AND ((CASE WHEN (cte0.c3 IS NOT NULL) THEN cte0.c3 WHEN (cte1.c3 IS NOT NULL) THEN cte1.c3 ELSE NULL::integer END) = cte2.c3))
20. 0.008 499.062 ↑ 69.2 13 1

Sort (cost=130.65..132.90 rows=899 width=32) (actual time=499.061..499.062 rows=13 loops=1)

  • Sort Key: (CASE WHEN (cte0.c2 IS NOT NULL) THEN cte0.c2 WHEN (cte1.c2 IS NOT NULL) THEN cte1.c2 ELSE NULL::integer END), (CASE WHEN (cte0.c3 IS NOT NULL) THEN cte0.c3 WHEN (cte1.c3 IS NOT NULL) THEN cte1.c3 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 26kB
21. 0.011 499.054 ↑ 69.2 13 1

Merge Full Join (cost=77.78..86.54 rows=899 width=32) (actual time=499.046..499.054 rows=13 loops=1)

  • Merge Cond: (((CASE WHEN (cte0.c2 IS NOT NULL) THEN cte0.c2 ELSE NULL::integer END) = cte1.c2) AND ((CASE WHEN (cte0.c3 IS NOT NULL) THEN cte0.c3 ELSE NULL::integer END) = cte1.c3))
22. 0.012 8.047 ↑ 20.1 13 1

Sort (cost=15.70..16.35 rows=261 width=16) (actual time=8.045..8.047 rows=13 loops=1)

  • Sort Key: (CASE WHEN (cte0.c2 IS NOT NULL) THEN cte0.c2 ELSE NULL::integer END), (CASE WHEN (cte0.c3 IS NOT NULL) THEN cte0.c3 ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 26kB
23. 8.035 8.035 ↑ 20.1 13 1

CTE Scan on meeting cte0 (cost=0.00..5.22 rows=261 width=16) (actual time=7.676..8.035 rows=13 loops=1)

24. 0.006 490.996 ↑ 69.2 13 1

Sort (cost=62.09..64.33 rows=899 width=16) (actual time=490.995..490.996 rows=13 loops=1)

  • Sort Key: cte1.c2, cte1.c3
  • Sort Method: quicksort Memory: 25kB
25. 490.990 490.990 ↑ 69.2 13 1

CTE Scan on email_sent cte1 (cost=0.00..17.98 rows=899 width=16) (actual time=489.963..490.990 rows=13 loops=1)

26. 0.010 1,015.450 ↑ 55.7 13 1

Sort (cost=48.87..50.68 rows=724 width=16) (actual time=1,015.449..1,015.450 rows=13 loops=1)

  • Sort Key: cte2.c2, cte2.c3
  • Sort Method: quicksort Memory: 25kB
27. 1,015.440 1,015.440 ↑ 55.7 13 1

CTE Scan on email_received cte2 (cost=0.00..14.48 rows=724 width=16) (actual time=1,014.422..1,015.440 rows=13 loops=1)

Planning time : 3.816 ms
Execution time : 1,519.972 ms