explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UnqB

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 6,273.090 ↑ 1.0 40 1

Limit (cost=209,397,740.57..209,397,740.67 rows=40 width=76) (actual time=6,273.084..6,273.090 rows=40 loops=1)

  • Buffers: shared hit=240,653, temp read=16,029 written=20,402
2.          

CTE filtered_accounts_cte

3. 15.043 205.156 ↑ 2.1 35,940 1

Hash Join (cost=59,298.97..84,990.69 rows=74,039 width=40) (actual time=140.938..205.156 rows=35,940 loops=1)

  • Hash Cond: (t1401_accountfact.sid = t2268_accountdim.sid)
  • Buffers: shared hit=61,958, temp read=190 written=188
4. 49.606 49.606 ↑ 1.4 35,940 1

Seq Scan on t1401_accountfact (cost=0.00..18,028.11 rows=51,628 width=4) (actual time=0.004..49.606 rows=35,940 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint))
  • Rows Removed by Filter: 422,961
  • Buffers: shared hit=10,949
5. 9.653 140.507 ↑ 2.1 35,941 1

Hash (cost=57,786.71..57,786.71 rows=74,421 width=40) (actual time=140.507..140.507 rows=35,941 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 1,801kB
  • Buffers: shared hit=51,009, temp written=134
6. 130.854 130.854 ↑ 2.1 35,941 1

Seq Scan on t2268_accountdim (cost=0.00..57,786.71 rows=74,421 width=40) (actual time=0.003..130.854 rows=35,941 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593173652000'::bigint) AND (end_stamp > '1593173652000'::bigint))
  • Rows Removed by Filter: 438,189
  • Buffers: shared hit=51,009
7. 1.568 6,273.085 ↑ 92.5 40 1

Sort (cost=209,312,749.88..209,312,759.13 rows=3,702 width=76) (actual time=6,273.083..6,273.085 rows=40 loops=1)

  • Sort Key: cte0.c3
  • Sort Method: top-N heapsort Memory: 30kB
  • Buffers: shared hit=240,653, temp read=16,029 written=20,402
8. 1.150 6,271.517 ↓ 1.1 4,039 1

Merge Anti Join (cost=206,990,584.68..209,312,632.86 rows=3,702 width=76) (actual time=5,628.738..6,271.517 rows=4,039 loops=1)

  • Merge Cond: (cte0.c1 = t2273_meetingactivityaccountfact.c9010_accountid)
  • Buffers: shared hit=240,653, temp read=16,029 written=20,402
9. 449.854 6,164.162 ↑ 1.3 5,838 1

GroupAggregate (cost=205,259,322.79..207,580,917.26 rows=7,404 width=76) (actual time=5,533.171..6,164.162 rows=5,838 loops=1)

  • Group Key: cte0.c1, cte0.c3
  • Buffers: shared hit=235,543, temp read=15,912 written=20,284
10. 1,376.406 5,714.308 ↑ 113.4 2,047,385 1

Sort (cost=205,259,322.79..205,839,702.90 rows=232,152,043 width=40) (actual time=5,533.066..5,714.308 rows=2,047,385 loops=1)

  • Sort Key: cte0.c1, cte0.c3
  • Sort Method: external sort Disk: 65,864kB
  • Buffers: shared hit=235,543, temp read=15,912 written=20,284
11. 305.634 4,337.902 ↑ 113.4 2,047,385 1

Merge Join (cost=144,117,469.98..147,609,590.15 rows=232,152,043 width=40) (actual time=3,489.569..4,337.902 rows=2,047,385 loops=1)

  • Merge Cond: (cte0.c1 = t2608_emailactivityaccountfact_copy_sentha.c9638_accountid)
  • Buffers: shared hit=235,543, temp read=7,679 written=12,051
12. 15.820 233.406 ↑ 2.1 35,940 1

Sort (cost=9,495.55..9,680.65 rows=74,039 width=36) (actual time=228.216..233.406 rows=35,940 loops=1)

  • Sort Key: cte0.c1
  • Sort Method: quicksort Memory: 3,470kB
  • Buffers: shared hit=61,958, temp read=190 written=188
13. 217.586 217.586 ↑ 2.1 35,940 1

CTE Scan on filtered_accounts_cte cte0 (cost=0.00..1,480.78 rows=74,039 width=36) (actual time=140.941..217.586 rows=35,940 loops=1)

  • Buffers: shared hit=61,958, temp read=190 written=188
14. 303.290 3,798.862 ↑ 1.9 2,047,385 1

Materialize (cost=144,107,974.43..144,126,913.07 rows=3,787,729 width=8) (actual time=3,261.346..3,798.862 rows=2,047,385 loops=1)

  • Buffers: shared hit=173,585, temp read=7,489 written=11,863
15. 721.529 3,495.572 ↑ 1.9 2,047,385 1

Sort (cost=144,107,974.43..144,117,443.75 rows=3,787,729 width=8) (actual time=3,261.343..3,495.572 rows=2,047,385 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_copy_sentha.c9638_accountid
  • Sort Method: external merge Disk: 36,112kB
  • Buffers: shared hit=173,585, temp read=7,489 written=7,524
16. 2,773.689 2,774.043 ↑ 1.9 2,047,385 1

Index Scan using idx_isinternal_sentha1 on t2608_emailactivityaccountfact_copy_sentha (cost=0.56..143,590,538.08 rows=3,787,729 width=8) (actual time=0.401..2,774.043 rows=2,047,385 loops=1)

  • Index Cond: ((c9660_isinternalinitiated = true) AND (end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Filter: (c9660_isinternalinitiated AND (c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND ((alternatives: SubPlan 2 or hashed SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5)))
  • Rows Removed by Filter: 3,341,649
  • Buffers: shared hit=173,585
17.          

SubPlan (for Index Scan)

18. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.32..15.78 rows=1 width=0) (never executed)

  • Hash Cond: (t859_userroledim.sid = t786_userrolehierarchy.c4812_user_role_id)
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim (cost=0.00..7.35 rows=29 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
20. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=4) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_37_t786_userrolehierarchy_sid_c4815_ancestor_role_id_end_st on t786_userrolehierarchy (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t2608_emailactivityaccountfact_copy_sentha.c9662_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
22. 0.099 0.296 ↓ 1.5 359 1

Hash Join (cost=23.72..97.23 rows=246 width=4) (actual time=0.106..0.296 rows=359 loops=1)

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
  • Buffers: shared hit=76
23. 0.115 0.150 ↑ 1.0 359 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_1 (cost=16.01..88.47 rows=364 width=8) (actual time=0.048..0.150 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
  • Buffers: shared hit=70
24. 0.035 0.035 ↑ 1.0 359 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.92 rows=364 width=0) (actual time=0.034..0.035 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=8
25. 0.007 0.047 ↓ 1.5 44 1

Hash (cost=7.35..7.35 rows=29 width=4) (actual time=0.047..0.047 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=6
26. 0.040 0.040 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_1 (cost=0.00..7.35 rows=29 width=4) (actual time=0.005..0.040 rows=44 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 84
  • Buffers: shared hit=6
27. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_14_t773_userdim_sid_end_stamp_start_stamp_null on t773_userdim (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t2608_emailactivityaccountfact_copy_sentha.c9662_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
28. 0.039 0.058 ↑ 2.5 27 1

Bitmap Heap Scan on t773_userdim t773_userdim_1 (cost=4.98..53.05 rows=68 width=4) (actual time=0.029..0.058 rows=27 loops=1)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=23
  • Buffers: shared hit=27
29. 0.019 0.019 ↑ 1.9 36 1

Bitmap Index Scan on idx_15_t773_userdim_c4570_userroleid_end_stamp_start_stamp_null (cost=0.00..4.97 rows=69 width=0) (actual time=0.019..0.019 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=4
30. 4.484 106.205 ↓ 1.1 1,861 1

Unique (cost=1,731,261.89..1,731,527.63 rows=1,710 width=4) (actual time=95.562..106.205 rows=1,861 loops=1)

  • Buffers: shared hit=5,110, temp read=117 written=118
31. 39.212 101.721 ↓ 1.3 67,710 1

Sort (cost=1,731,261.89..1,731,394.76 rows=53,149 width=4) (actual time=95.561..101.721 rows=67,710 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact.c9010_accountid
  • Sort Method: external merge Disk: 936kB
  • Buffers: shared hit=5,110, temp read=117 written=118
32. 62.141 62.509 ↓ 1.3 67,811 1

Index Scan using "idx_activitydate_issharedp_del_P_sentha" on t2273_meetingactivityaccountfact (cost=0.42..1,727,090.29 rows=53,149 width=4) (actual time=0.405..62.509 rows=67,811 loops=1)

  • Index Cond: ((c8986_activitydate >= '1561532400000'::bigint) AND (c8986_activitydate < '1593241200000'::bigint) AND (end_stamp > '1593173652000'::bigint) AND (start_stamp <= '1593173652000'::bigint))
  • Filter: ((alternatives: SubPlan 6 or hashed SubPlan 7) OR (alternatives: SubPlan 8 or hashed SubPlan 9))
  • Rows Removed by Filter: 6,979
  • Buffers: shared hit=5,110
33.          

SubPlan (for Index Scan)

34. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.32..15.78 rows=1 width=0) (never executed)

  • Hash Cond: (t859_userroledim_2.sid = t786_userrolehierarchy_2.c4812_user_role_id)
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on t859_userroledim t859_userroledim_2 (cost=0.00..7.35 rows=29 width=4) (never executed)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
36. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.30..8.30 rows=1 width=4) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_37_t786_userrolehierarchy_sid_c4815_ancestor_role_id_end_st on t786_userrolehierarchy t786_userrolehierarchy_2 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t2273_meetingactivityaccountfact.c9004_activityownersfuserid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
38. 0.107 0.311 ↓ 1.5 359 1

Hash Join (cost=23.72..97.23 rows=246 width=4) (actual time=0.113..0.311 rows=359 loops=1)

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
  • Buffers: shared hit=76
39. 0.118 0.155 ↑ 1.0 359 1

Bitmap Heap Scan on t786_userrolehierarchy t786_userrolehierarchy_3 (cost=16.01..88.47 rows=364 width=8) (actual time=0.051..0.155 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
  • Buffers: shared hit=70
40. 0.037 0.037 ↑ 1.0 359 1

Bitmap Index Scan on idx_35_t786_userrolehierarchy_c4815_ancestor_role_id_end_stamp_ (cost=0.00..15.92 rows=364 width=0) (actual time=0.037..0.037 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=8
41. 0.009 0.049 ↓ 1.5 44 1

Hash (cost=7.35..7.35 rows=29 width=4) (actual time=0.049..0.049 rows=44 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=6
42. 0.040 0.040 ↓ 1.5 44 1

Seq Scan on t859_userroledim t859_userroledim_3 (cost=0.00..7.35 rows=29 width=4) (actual time=0.006..0.040 rows=44 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c4994_portaltype IS NULL) OR (c4994_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 84
  • Buffers: shared hit=6
43. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_14_t773_userdim_sid_end_stamp_start_stamp_null on t773_userdim t773_userdim_2 (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t2273_meetingactivityaccountfact.c9004_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
44. 0.040 0.057 ↑ 2.5 27 1

Bitmap Heap Scan on t773_userdim t773_userdim_3 (cost=4.98..53.05 rows=68 width=4) (actual time=0.027..0.057 rows=27 loops=1)

  • Recheck Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=23
  • Buffers: shared hit=27
45. 0.017 0.017 ↑ 1.9 36 1

Bitmap Index Scan on idx_15_t773_userdim_c4570_userroleid_end_stamp_start_stamp_null (cost=0.00..4.97 rows=69 width=0) (actual time=0.017..0.017 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=4
Planning time : 3.862 ms
Execution time : 6,296.262 ms