explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AOX1

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 3,859.942 ↑ 2,664.7 13 1

Sort (cost=836,933.76..837,020.36 rows=34,641 width=32) (actual time=3,859.942..3,859.942 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 email_received

3. 13.898 1,911.915 ↑ 2,258.5 13 1

GroupAggregate (cost=406,887.47..408,429.24 rows=29,360 width=16) (actual time=1,896.243..1,911.915 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)
4. 17.251 1,898.017 ↓ 1.1 40,462 1

Sort (cost=406,887.47..406,979.31 rows=36,737 width=12) (actual time=1,893.887..1,898.017 rows=40,462 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: 3,262kB
5. 83.391 1,880.766 ↓ 1.1 40,462 1

Gather (cost=1,000.00..404,101.89 rows=36,737 width=12) (actual time=46.252..1,880.766 rows=40,462 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 1,797.375 1,797.375 ↑ 1.1 13,487 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact (cost=0.00..398,785.29 rows=15,307 width=12) (actual time=65.457..1,797.375 rows=13,487 loops=3)

  • Filter: ((NOT deleted) AND (NOT c8043_isinternalinitiated) AND c8040_isshared AND (c8036_activitydate >= '1561642006860'::bigint) AND (c8036_activitydate < '1593178006860'::bigint) AND (end_stamp = '32503680000000'::bigint) AND (c8045_activityownersfuserid = 97))
  • Rows Removed by Filter: 4,348,238
7.          

CTE email_sent

8. 13.976 1,907.321 ↑ 2,664.7 13 1

GroupAggregate (cost=408,792.44..410,634.53 rows=34,641 width=16) (actual time=1,892.241..1,907.321 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)
9. 16.829 1,893.345 ↓ 1.1 50,461 1

Sort (cost=408,792.44..408,906.55 rows=45,645 width=12) (actual time=1,889.951..1,893.345 rows=50,461 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: 3,731kB
10. 97.218 1,876.516 ↓ 1.1 50,461 1

Gather (cost=1,000.00..405,259.93 rows=45,645 width=12) (actual time=25.832..1,876.516 rows=50,461 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 1,779.298 1,779.298 ↑ 1.1 16,820 3 / 3

Parallel Seq Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=0.00..398,896.65 rows=19,019 width=12) (actual time=30.532..1,779.298 rows=16,820 loops=3)

  • Filter: ((NOT deleted) AND c8043_isinternalinitiated AND c8040_isshared AND (c8036_activitydate >= '1561642006860'::bigint) AND (c8036_activitydate < '1593178006860'::bigint) AND (end_stamp = '32503680000000'::bigint) AND (c8045_activityownersfuserid = 97))
  • Rows Removed by Filter: 4,344,905
12.          

CTE meeting

13. 0.745 40.559 ↑ 192.4 13 1

GroupAggregate (cost=5,217.62..5,349.66 rows=2,501 width=16) (actual time=39.760..40.559 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)
14. 0.966 39.814 ↓ 1.1 3,372 1

Sort (cost=5,217.62..5,225.62 rows=3,200 width=12) (actual time=39.652..39.814 rows=3,372 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: 255kB
15. 38.848 38.848 ↓ 1.1 3,372 1

Seq Scan on t1662_meetingactivityaccountfact (cost=0.00..5,031.32 rows=3,200 width=12) (actual time=2.524..38.848 rows=3,372 loops=1)

  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1561642006860'::bigint) AND (c7794_activitydate < '1593178006860'::bigint) AND (end_stamp = '32503680000000'::bigint) AND (c7807_activityownersfuserid = 97))
  • Rows Removed by Filter: 117,808
16. 0.018 3,859.933 ↑ 2,664.7 13 1

Merge Full Join (cost=9,608.14..9,908.36 rows=34,641 width=32) (actual time=3,859.925..3,859.933 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))
17. 0.010 3,819.339 ↑ 2,664.7 13 1

Sort (cost=9,416.96..9,503.56 rows=34,641 width=32) (actual time=3,819.338..3,819.339 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
18. 0.013 3,819.329 ↑ 2,664.7 13 1

Merge Full Join (cost=6,070.73..6,804.99 rows=34,641 width=32) (actual time=3,819.320..3,819.329 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))
19. 0.018 1,911.961 ↑ 2,258.5 13 1

Sort (cost=2,765.94..2,839.34 rows=29,360 width=16) (actual time=1,911.959..1,911.961 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
20. 1,911.943 1,911.943 ↑ 2,258.5 13 1

CTE Scan on email_received cte0 (cost=0.00..587.20 rows=29,360 width=16) (actual time=1,896.251..1,911.943 rows=13 loops=1)

21. 0.013 1,907.355 ↑ 2,664.7 13 1

Sort (cost=3,304.78..3,391.39 rows=34,641 width=16) (actual time=1,907.354..1,907.355 rows=13 loops=1)

  • Sort Key: cte1.c2, cte1.c3
  • Sort Method: quicksort Memory: 25kB
22. 1,907.342 1,907.342 ↑ 2,664.7 13 1

CTE Scan on email_sent cte1 (cost=0.00..692.82 rows=34,641 width=16) (actual time=1,892.244..1,907.342 rows=13 loops=1)

23. 0.011 40.576 ↑ 192.4 13 1

Sort (cost=191.18..197.43 rows=2,501 width=16) (actual time=40.576..40.576 rows=13 loops=1)

  • Sort Key: cte2.c2, cte2.c3
  • Sort Method: quicksort Memory: 25kB
24. 40.565 40.565 ↑ 192.4 13 1

CTE Scan on meeting cte2 (cost=0.00..50.02 rows=2,501 width=16) (actual time=39.762..40.565 rows=13 loops=1)

Planning time : 4.091 ms
Execution time : 3,861.347 ms