explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DeSi

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 2,710.939 ↑ 1.0 40 1

Limit (cost=24,092,828.53..24,099,004.07 rows=40 width=76) (actual time=2,710.817..2,710.939 rows=40 loops=1)

  • Buffers: shared hit=243,432, temp read=485 written=484
2.          

CTE filtered_accounts_cte

3. 14.971 181.672 ↑ 1.5 35,856 1

Hash Join (cost=73,196.49..1,568,434.48 rows=55,530 width=40) (actual time=152.884..181.672 rows=35,856 loops=1)

  • Hash Cond: (t1401_accountfact.sid = t2268_accountdim.sid)
  • Buffers: shared hit=54,567, temp read=190 written=188
4. 14.195 25.765 ↑ 1.1 35,856 1

Bitmap Heap Scan on t1401_accountfact (cost=13,897.51..1,503,241.29 rows=38,721 width=4) (actual time=11.698..25.765 rows=35,856 loops=1)

  • Recheck Cond: ((end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Filter: ((NOT deleted) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)))
  • Rows Removed by Filter: 23,736
  • Heap Blocks: exact=1,137
  • Buffers: shared hit=3,558
5. 11.211 11.211 ↑ 1.0 59,616 1

Bitmap Index Scan on idx_933_t1401_accountfact_sid_end_stamp_start_stamp_null (cost=0.00..13,887.83 rows=61,341 width=0) (actual time=11.210..11.211 rows=59,616 loops=1)

  • Index Cond: ((end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Buffers: shared hit=2,318
6.          

SubPlan (for Bitmap Heap Scan)

7. 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)
8. 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))
9. 0.000 0.000 ↓ 0.0 0

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

10. 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 = t1401_accountfact.c7136_ownerid) AND (c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
11. 0.105 0.297 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_1.c4812_user_role_id = t859_userroledim_1.sid)
  • Buffers: shared hit=76
12. 0.115 0.147 ↑ 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.043..0.147 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
  • Buffers: shared hit=70
13. 0.032 0.032 ↑ 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.032..0.032 rows=359 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=6
15. 0.038 0.038 ↓ 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.038 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
16. 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 = t1401_accountfact.c7136_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c4570_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c4581_usertype = 'Standard'::text))
17. 0.041 0.062 ↑ 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.032..0.062 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
18. 0.021 0.021 ↑ 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.021..0.021 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=4
19. 9.735 140.936 ↑ 2.1 35,941 1

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

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

Seq Scan on t2268_accountdim (cost=0.00..57,786.71 rows=74,422 width=40) (actual time=0.004..131.201 rows=35,941 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1593172558000'::bigint) AND (end_stamp > '1593172558000'::bigint))
  • Rows Removed by Filter: 438,189
  • Buffers: shared hit=51,009
21.          

CTE account_activity_count_cte_meeting_current

22. 5.283 105.941 ↓ 1.1 1,861 1

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

  • Buffers: shared hit=5,110, temp read=117 written=118
23. 40.470 100.658 ↓ 1.3 67,811 1

Sort (cost=1,731,261.89..1,731,394.76 rows=53,149 width=4) (actual time=94.687..100.658 rows=67,811 loops=1)

  • Sort Key: t2273_meetingactivityaccountfact.c9010_accountid
  • Sort Method: external merge Disk: 936kB
  • Buffers: shared hit=5,110, temp read=117 written=118
24. 59.824 60.188 ↓ 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.403..60.188 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
25.          

SubPlan (for Index Scan)

26. 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)
27. 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))
28. 0.000 0.000 ↓ 0.0 0

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

29. 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))
30. 0.097 0.303 ↓ 1.5 359 1

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

  • Hash Cond: (t786_userrolehierarchy_3.c4812_user_role_id = t859_userroledim_3.sid)
  • Buffers: shared hit=76
31. 0.117 0.157 ↑ 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.054..0.157 rows=359 loops=1)

  • Recheck Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=62
  • Buffers: shared hit=70
32. 0.040 0.040 ↑ 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.040..0.040 rows=359 loops=1)

  • Index Cond: ((c4815_ancestor_role_id = 12) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=8
33. 0.010 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
34. 0.039 0.039 ↓ 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.039 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
35. 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))
36. 0.040 0.061 ↑ 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.030..0.061 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
37. 0.021 0.021 ↑ 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.021..0.021 rows=36 loops=1)

  • Index Cond: ((c4570_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Buffers: shared hit=4
38. 0.099 2,710.934 ↑ 138.8 40 1

GroupAggregate (cost=20,792,866.42..21,650,184.71 rows=5,553 width=76) (actual time=2,710.816..2,710.934 rows=40 loops=1)

  • Group Key: cte0.c3, cte0.c1
  • Buffers: shared hit=243,432, temp read=485 written=484
39. 121.380 2,710.835 ↑ 323,495.4 265 1

Sort (cost=20,792,866.42..21,007,182.11 rows=85,726,276 width=40) (actual time=2,710.796..2,710.835 rows=265 loops=1)

  • Sort Key: cte0.c3, cte0.c1
  • Sort Method: external merge Disk: 1,424kB
  • Buffers: shared hit=243,432, temp read=485 written=484
40. 10.268 2,589.455 ↑ 1,790.5 47,879 1

Merge Join (cost=1,173,970.79..2,464,804.80 rows=85,726,276 width=40) (actual time=2,565.566..2,589.455 rows=47,879 loops=1)

  • Merge Cond: (cte0.c1 = t2608_emailactivityaccountfact_copy_sentha.c9638_accountid)
  • Buffers: shared hit=243,432, temp read=307 written=306
41. 14.791 208.938 ↑ 1.5 35,847 1

Sort (cost=5,486.64..5,625.46 rows=55,530 width=36) (actual time=204.671..208.938 rows=35,847 loops=1)

  • Sort Key: cte0.c1
  • Sort Method: quicksort Memory: 3,464kB
  • Buffers: shared hit=54,567, temp read=190 written=188
42. 194.147 194.147 ↑ 1.5 35,856 1

CTE Scan on filtered_accounts_cte cte0 (cost=0.00..1,110.60 rows=55,530 width=36) (actual time=152.887..194.147 rows=35,856 loops=1)

  • Buffers: shared hit=54,567, temp read=190 written=188
43. 5.497 2,370.249 ↑ 38.9 47,925 1

Materialize (cost=1,168,484.15..1,177,808.60 rows=1,864,890 width=8) (actual time=2,360.890..2,370.249 rows=47,925 loops=1)

  • Buffers: shared hit=188,865, temp read=117 written=118
44. 13.362 2,364.752 ↑ 38.9 47,925 1

Sort (cost=1,168,484.15..1,173,146.38 rows=1,864,890 width=8) (actual time=2,360.886..2,364.752 rows=47,925 loops=1)

  • Sort Key: t2608_emailactivityaccountfact_copy_sentha.c9638_accountid
  • Sort Method: quicksort Memory: 3,612kB
  • Buffers: shared hit=188,865, temp read=117 written=118
45. 464.078 2,351.390 ↑ 38.9 47,925 1

Hash Anti Join (cost=220,112.14..948,752.21 rows=1,864,890 width=8) (actual time=493.104..2,351.390 rows=47,925 loops=1)

  • Hash Cond: (t2608_emailactivityaccountfact_copy_sentha.c9638_accountid = account_activity_count_cte_meeting_current.c1)
  • Buffers: shared hit=188,865, temp read=117 written=118
46. 1,404.143 1,780.823 ↓ 1.1 3,924,982 1

Bitmap Heap Scan on t2608_emailactivityaccountfact_copy_sentha (cost=220,056.56..713,021.16 rows=3,729,780 width=8) (actual time=386.586..1,780.823 rows=3,924,982 loops=1)

  • Recheck Cond: ((c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint) AND c9657_isshared AND (NOT deleted) AND (NOT c9660_isinternalinitiated))
  • Rows Removed by Index Recheck: 2,076,905
  • Heap Blocks: exact=53,571 lossy=99,696
  • Buffers: shared hit=183,755
47. 376.680 376.680 ↓ 1.1 3,924,982 1

Bitmap Index Scan on "idx_isinternla_actDate_Sentha_P2_false" (cost=0.00..219,124.12 rows=3,729,780 width=0) (actual time=376.680..376.680 rows=3,924,982 loops=1)

  • Index Cond: ((c9653_activitydate >= '1561532400000'::bigint) AND (c9653_activitydate < '1593241200000'::bigint) AND (end_stamp > '1593172558000'::bigint) AND (start_stamp <= '1593172558000'::bigint))
  • Buffers: shared hit=30,488
48. 0.235 106.489 ↓ 1.1 1,861 1

Hash (cost=34.20..34.20 rows=1,710 width=4) (actual time=106.488..106.489 rows=1,861 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
  • Buffers: shared hit=5,110, temp read=117 written=118
49. 106.254 106.254 ↓ 1.1 1,861 1

CTE Scan on account_activity_count_cte_meeting_current (cost=0.00..34.20 rows=1,710 width=4) (actual time=94.692..106.254 rows=1,861 loops=1)

  • Buffers: shared hit=5,110, temp read=117 written=118
Planning time : 3.696 ms
Execution time : 2,714.358 ms