explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yqBb

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 31.064 ↑ 1.2 6 1

Sort (cost=24,538.03..24,538.05 rows=7 width=32) (actual time=31.064..31.064 rows=6 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: 25kB
2.          

CTE meeting

3. 0.024 5.634 ↓ 4.0 4 1

GroupAggregate (cost=141.41..141.46 rows=1 width=16) (actual time=5.623..5.634 rows=4 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.020 5.610 ↓ 21.0 21 1

Sort (cost=141.41..141.41 rows=1 width=12) (actual time=5.608..5.610 rows=21 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: 25kB
5. 5.590 5.590 ↓ 21.0 21 1

Index Scan using t1662_meetingactivityaccountfact_917_ptimestamp on t1662_meetingactivityaccountfact (cost=0.42..141.40 rows=1 width=12) (actual time=3.847..5.590 rows=21 loops=1)

  • Index Cond: (c7809_accountid = 450)
  • Filter: ((NOT deleted) AND c7805_isshared AND (c7794_activitydate >= '1561639478620'::bigint) AND (c7794_activitydate < '1593175478620'::bigint) AND (c7807_activityownersfuserid = c7812_accountid_accountownerid))
  • Rows Removed by Filter: 66
6.          

CTE email_sent

7. 0.052 2.202 ↑ 1.2 6 1

GroupAggregate (cost=12,197.68..12,198.03 rows=7 width=16) (actual time=2.156..2.202 rows=6 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)
8. 0.048 2.150 ↓ 18.0 126 1

Sort (cost=12,197.68..12,197.70 rows=7 width=12) (actual time=2.140..2.150 rows=126 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: 30kB
9. 1.875 2.102 ↓ 18.0 126 1

Bitmap Heap Scan on t1816_emailactivityaccountfact (cost=65.14..12,197.58 rows=7 width=12) (actual time=1.728..2.102 rows=126 loops=1)

  • Recheck Cond: ((c8021_accountid = 450) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND c8043_isinternalinitiated AND c8040_isshared AND (c8036_activitydate >= '1561639478620'::bigint) AND (c8036_activitydate < '1593175478620'::bigint) AND (c8045_activityownersfuserid = c8024_accountid_accountownerid))
  • Rows Removed by Filter: 3,080
  • Heap Blocks: exact=331
10. 0.227 0.227 ↑ 1.0 3,206 1

Bitmap Index Scan on t1816_emailactivityaccountfact_983_ptimestamp (cost=0.00..65.14 rows=3,294 width=0) (actual time=0.227..0.227 rows=3,206 loops=1)

  • Index Cond: (c8021_accountid = 450)
11.          

CTE email_received

12. 0.055 23.150 ↑ 1.2 5 1

GroupAggregate (cost=12,197.63..12,197.93 rows=6 width=16) (actual time=23.108..23.150 rows=5 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)
13. 0.049 23.095 ↓ 18.8 113 1

Sort (cost=12,197.63..12,197.65 rows=6 width=12) (actual time=23.085..23.095 rows=113 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: 30kB
14. 1.990 23.046 ↓ 18.8 113 1

Bitmap Heap Scan on t1816_emailactivityaccountfact t1816_emailactivityaccountfact_1 (cost=65.14..12,197.55 rows=6 width=12) (actual time=21.624..23.046 rows=113 loops=1)

  • Recheck Cond: ((c8021_accountid = 450) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (NOT c8043_isinternalinitiated) AND c8040_isshared AND (c8036_activitydate >= '1561639478620'::bigint) AND (c8036_activitydate < '1593175478620'::bigint) AND (c8045_activityownersfuserid = c8024_accountid_accountownerid))
  • Rows Removed by Filter: 3,093
  • Heap Blocks: exact=331
15. 21.056 21.056 ↑ 1.0 3,206 1

Bitmap Index Scan on t1816_emailactivityaccountfact_983_ptimestamp (cost=0.00..65.14 rows=3,294 width=0) (actual time=21.056..21.056 rows=3,206 loops=1)

  • Index Cond: (c8021_accountid = 450)
16. 0.022 31.054 ↑ 1.2 6 1

Hash Full Join (cost=0.24..0.51 rows=7 width=32) (actual time=30.990..31.054 rows=6 loops=1)

  • Hash 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.018 7.870 ↑ 1.2 6 1

Hash Full Join (cost=0.04..0.24 rows=7 width=32) (actual time=7.816..7.870 rows=6 loops=1)

  • Hash Cond: ((cte1.c2 = CASE WHEN (cte0.c2 IS NOT NULL) THEN cte0.c2 ELSE NULL::integer END) AND (cte1.c3 = CASE WHEN (cte0.c3 IS NOT NULL) THEN cte0.c3 ELSE NULL::integer END))
18. 2.206 2.206 ↑ 1.2 6 1

CTE Scan on email_sent cte1 (cost=0.00..0.14 rows=7 width=16) (actual time=2.158..2.206 rows=6 loops=1)

19. 0.008 5.646 ↓ 4.0 4 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=5.646..5.646 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 5.638 5.638 ↓ 4.0 4 1

CTE Scan on meeting cte0 (cost=0.00..0.02 rows=1 width=16) (actual time=5.625..5.638 rows=4 loops=1)

21. 0.007 23.162 ↑ 1.2 5 1

Hash (cost=0.12..0.12 rows=6 width=16) (actual time=23.162..23.162 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 23.155 23.155 ↑ 1.2 5 1

CTE Scan on email_received cte2 (cost=0.00..0.12 rows=6 width=16) (actual time=23.110..23.155 rows=5 loops=1)

Planning time : 2.056 ms
Execution time : 31.358 ms