explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 51oK

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 252.708 ↑ 1.0 40 1

Limit (cost=411,985,494.20..411,985,494.30 rows=40 width=266) (actual time=252.703..252.708 rows=40 loops=1)

2.          

CTE filtered_accounts_cte

3. 0.098 249.951 ↑ 768.9 51 1

Nested Loop (cost=0.85..411,969,041.26 rows=39,216 width=139) (actual time=1.855..249.951 rows=51 loops=1)

4. 1.430 249.496 ↑ 267.1 51 1

Nested Loop (cost=0.42..411,899,692.75 rows=13,621 width=34) (actual time=1.837..249.496 rows=51 loops=1)

  • Join Filter: (t12227_accountfact.c66438_account_ownerid = t9120_userdim_6.sid)
  • Rows Removed by Join Filter: 20,451
5. 93.690 246.791 ↑ 184.5 51 1

Index Scan using t12227_accountfact_sidendstampunique on t12227_accountfact (cost=0.42..411,844,298.36 rows=9,410 width=24) (actual time=1.517..246.791 rows=51 loops=1)

  • Index Cond: (end_stamp > '1602861867000'::bigint)
  • Filter: ((NOT deleted) AND (start_stamp <= '1602861867000'::bigint) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)) AND (SubPlan 9) AND (SubPlan 14))
  • Rows Removed by Filter: 61,110
6.          

SubPlan (for Index Scan)

7. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=16.91..24.38 rows=1 width=0) (never executed)

  • Hash Cond: (t9123_userroledim.sid = t10977_userrolehierarchy.c61341_user_role_id)
8. 0.000 0.000 ↓ 0.0 0

Seq Scan on t9123_userroledim (cost=0.00..7.28 rows=50 width=4) (never executed)

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

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

10. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_6301_t10977_userrolehierarchy_sid_c61342_ancestor_role_id_e on t10977_userrolehierarchy (cost=0.28..16.89 rows=1 width=4) (never executed)

  • Index Cond: ((sid = t12227_accountfact.c66438_account_ownerid) AND (c61342_ancestor_role_id = ANY ('{661,5,13}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
11. 0.080 0.286 ↑ 1.3 382 1

Hash Join (cost=24.84..99.46 rows=478 width=4) (actual time=0.104..0.286 rows=382 loops=1)

  • Hash Cond: (t10977_userrolehierarchy_1.c61341_user_role_id = t9123_userroledim_1.sid)
12. 0.123 0.163 ↑ 1.0 382 1

Bitmap Heap Scan on t10977_userrolehierarchy t10977_userrolehierarchy_1 (cost=16.94..90.40 rows=398 width=8) (actual time=0.055..0.163 rows=382 loops=1)

  • Recheck Cond: ((c61342_ancestor_role_id = ANY ('{661,5,13}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
  • Heap Blocks: exact=67
13. 0.040 0.040 ↑ 1.0 385 1

Bitmap Index Scan on idx_6300_t10977_userrolehierarchy_c61342_ancestor_role_id_end_s (cost=0.00..16.84 rows=398 width=0) (actual time=0.040..0.040 rows=385 loops=1)

  • Index Cond: ((c61342_ancestor_role_id = ANY ('{661,5,13}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
14. 0.008 0.043 ↑ 1.1 45 1

Hash (cost=7.28..7.28 rows=50 width=4) (actual time=0.043..0.043 rows=45 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
15. 0.035 0.035 ↑ 1.1 45 1

Seq Scan on t9123_userroledim t9123_userroledim_1 (cost=0.00..7.28 rows=50 width=4) (actual time=0.006..0.035 rows=45 loops=1)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c54405_portaltype IS NULL) OR (c54405_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 85
16. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((sid = t12227_accountfact.c66438_account_ownerid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c54370_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c54396_usertype = 'Standard'::text))
17. 0.033 0.047 ↑ 2.6 27 1

Bitmap Heap Scan on t9120_userdim t9120_userdim_1 (cost=4.99..53.08 rows=69 width=4) (actual time=0.021..0.047 rows=27 loops=1)

  • Recheck Cond: ((c54370_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c54396_usertype = 'Standard'::text))
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=22
18. 0.014 0.014 ↑ 1.9 36 1

Bitmap Index Scan on idx_4430_t9120_userdim_c54370_userroleid_end_stamp_start_stamp_ (cost=0.00..4.98 rows=70 width=0) (actual time=0.014..0.014 rows=36 loops=1)

  • Index Cond: ((c54370_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
19. 139.808 141.848 ↓ 0.0 0 35,462

Index Scan using idx_8122_t12247_emailactivityaccountfact_c66670_accountid_end_s on t12247_emailactivityaccountfact (cost=0.42..3,377.01 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=35,462)

  • Index Cond: ((c66670_accountid = t12227_accountfact.sid) AND (end_stamp > '1602861867000'::bigint) AND (start_stamp <= '1602861867000'::bigint))
  • Filter: ((NOT deleted) AND (NOT c66624_isinternalinitiated) AND c66618_isshared AND (c66673_activitydate >= '1602226800000'::bigint) AND (c66673_activitydate < '1602918000000'::bigint) AND (c66665_activityownersfuserid = c66632_accountid_ownerid) AND ((alternatives: SubPlan 5 or hashed SubPlan 6) OR (alternatives: SubPlan 7 or hashed SubPlan 8)))
  • Rows Removed by Filter: 4
20.          

SubPlan (for Index Scan)

21. 0.360 2.040 ↑ 1.0 1 60

Hash Join (cost=16.91..24.38 rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=60)

  • Hash Cond: (t9123_userroledim_2.sid = t10977_userrolehierarchy_2.c61341_user_role_id)
22. 1.140 1.140 ↑ 1.5 34 60

Seq Scan on t9123_userroledim t9123_userroledim_2 (cost=0.00..7.28 rows=50 width=4) (actual time=0.002..0.019 rows=34 loops=60)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c54405_portaltype IS NULL) OR (c54405_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 59
23. 0.120 0.540 ↑ 1.0 1 60

Hash (cost=16.89..16.89 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=60)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.420 0.420 ↑ 1.0 1 60

Index Scan using idx_6301_t10977_userrolehierarchy_sid_c61342_ancestor_role_id_e on t10977_userrolehierarchy t10977_userrolehierarchy_2 (cost=0.28..16.89 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=60)

  • Index Cond: ((sid = t12247_emailactivityaccountfact.c66665_activityownersfuserid) AND (c61342_ancestor_role_id = ANY ('{661,5,13}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
25. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=24.84..99.46 rows=478 width=4) (never executed)

  • Hash Cond: (t10977_userrolehierarchy_3.c61341_user_role_id = t9123_userroledim_3.sid)
26. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t10977_userrolehierarchy t10977_userrolehierarchy_3 (cost=16.94..90.40 rows=398 width=8) (never executed)

  • Recheck Cond: ((c61342_ancestor_role_id = ANY ('{661,5,13}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
27. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_6300_t10977_userrolehierarchy_c61342_ancestor_role_id_end_s (cost=0.00..16.84 rows=398 width=0) (never executed)

  • Index Cond: ((c61342_ancestor_role_id = ANY ('{661,5,13}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
28. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.28..7.28 rows=50 width=4) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Seq Scan on t9123_userroledim t9123_userroledim_3 (cost=0.00..7.28 rows=50 width=4) (never executed)

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

Index Scan using t9120_userdim_sidendstampunique on t9120_userdim t9120_userdim_2 (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t12247_emailactivityaccountfact.c66665_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c54370_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c54396_usertype = 'Standard'::text))
31. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t9120_userdim t9120_userdim_3 (cost=4.99..53.08 rows=69 width=4) (never executed)

  • Recheck Cond: ((c54370_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c54396_usertype = 'Standard'::text))
32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_4430_t9120_userdim_c54370_userroleid_end_stamp_start_stamp_ (cost=0.00..4.98 rows=70 width=0) (never executed)

  • Index Cond: ((c54370_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
33. 9.543 10.920 ↑ 1.0 1 60

Index Scan using idx_8122_t12247_emailactivityaccountfact_c66670_accountid_end_s on t12247_emailactivityaccountfact t12247_emailactivityaccountfact_1 (cost=0.42..3,377.01 rows=1 width=0) (actual time=0.182..0.182 rows=1 loops=60)

  • Index Cond: ((c66670_accountid = t12227_accountfact.sid) AND (end_stamp > '1602861867000'::bigint) AND (start_stamp <= '1602861867000'::bigint))
  • Filter: ((NOT deleted) AND c66624_isinternalinitiated AND c66618_isshared AND (c66673_activitydate >= '1602226800000'::bigint) AND (c66673_activitydate < '1602918000000'::bigint) AND (c66665_activityownersfuserid = c66632_accountid_ownerid) AND ((alternatives: SubPlan 10 or hashed SubPlan 11) OR (alternatives: SubPlan 12 or hashed SubPlan 13)))
  • Rows Removed by Filter: 203
34.          

SubPlan (for Index Scan)

35. 0.255 1.377 ↑ 1.0 1 51

Hash Join (cost=16.91..24.38 rows=1 width=0) (actual time=0.027..0.027 rows=1 loops=51)

  • Hash Cond: (t9123_userroledim_4.sid = t10977_userrolehierarchy_4.c61341_user_role_id)
36. 0.816 0.816 ↑ 1.5 33 51

Seq Scan on t9123_userroledim t9123_userroledim_4 (cost=0.00..7.28 rows=50 width=4) (actual time=0.001..0.016 rows=33 loops=51)

  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND ((c54405_portaltype IS NULL) OR (c54405_portaltype = 'None'::text)) AND (end_stamp = '32503680000000'::bigint))
  • Rows Removed by Filter: 57
37. 0.051 0.306 ↑ 1.0 1 51

Hash (cost=16.89..16.89 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=51)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
38. 0.255 0.255 ↑ 1.0 1 51

Index Scan using idx_6301_t10977_userrolehierarchy_sid_c61342_ancestor_role_id_e on t10977_userrolehierarchy t10977_userrolehierarchy_4 (cost=0.28..16.89 rows=1 width=4) (actual time=0.003..0.005 rows=1 loops=51)

  • Index Cond: ((sid = t12247_emailactivityaccountfact_1.c66665_activityownersfuserid) AND (c61342_ancestor_role_id = ANY ('{661,5,13}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
39. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=24.84..99.46 rows=478 width=4) (never executed)

  • Hash Cond: (t10977_userrolehierarchy_5.c61341_user_role_id = t9123_userroledim_5.sid)
40. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t10977_userrolehierarchy t10977_userrolehierarchy_5 (cost=16.94..90.40 rows=398 width=8) (never executed)

  • Recheck Cond: ((c61342_ancestor_role_id = ANY ('{661,5,13}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
41. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_6300_t10977_userrolehierarchy_c61342_ancestor_role_id_end_s (cost=0.00..16.84 rows=398 width=0) (never executed)

  • Index Cond: ((c61342_ancestor_role_id = ANY ('{661,5,13}'::integer[])) AND (end_stamp = '32503680000000'::bigint))
42. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.28..7.28 rows=50 width=4) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Seq Scan on t9123_userroledim t9123_userroledim_5 (cost=0.00..7.28 rows=50 width=4) (never executed)

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

Index Scan using t9120_userdim_sidendstampunique on t9120_userdim t9120_userdim_4 (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((sid = t12247_emailactivityaccountfact_1.c66665_activityownersfuserid) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (c54370_userroleid IS NULL) AND (sid <> '-2'::integer) AND (c54396_usertype = 'Standard'::text))
45. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on t9120_userdim t9120_userdim_5 (cost=4.99..53.08 rows=69 width=4) (never executed)

  • Recheck Cond: ((c54370_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
  • Filter: ((NOT deleted) AND (sid <> '-2'::integer) AND (c54396_usertype = 'Standard'::text))
46. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_4430_t9120_userdim_c54370_userroleid_end_stamp_start_stamp_ (cost=0.00..4.98 rows=70 width=0) (never executed)

  • Index Cond: ((c54370_userroleid IS NULL) AND (end_stamp = '32503680000000'::bigint))
47. 1.022 1.275 ↓ 1.0 402 51

Materialize (cost=0.00..64.57 rows=392 width=18) (actual time=0.000..0.025 rows=402 loops=51)

48. 0.253 0.253 ↓ 1.0 402 1

Seq Scan on t9120_userdim t9120_userdim_6 (cost=0.00..62.61 rows=392 width=18) (actual time=0.002..0.253 rows=402 loops=1)

  • Filter: ((NOT deleted) AND (start_stamp <= '1602861867000'::bigint) AND (end_stamp > '1602861867000'::bigint))
  • Rows Removed by Filter: 787
49. 0.357 0.357 ↑ 1.0 1 51

Index Scan using t12305_accountdim_sidendstampunique on t12305_accountdim (cost=0.42..5.08 rows=1 width=109) (actual time=0.006..0.007 rows=1 loops=51)

  • Index Cond: ((sid = t12227_accountfact.sid) AND (end_stamp > '1602861867000'::bigint))
  • Filter: ((NOT deleted) AND (start_stamp <= '1602861867000'::bigint))
50. 0.078 252.703 ↑ 392.2 40 1

Sort (cost=16,452.94..16,492.16 rows=15,688 width=266) (actual time=252.702..252.703 rows=40 loops=1)

  • Sort Key: (count(DISTINCT t12298_meetingactivityaccountfact.c67685_activitychecksum)) DESC NULLS LAST, cte0.c3 NULLS FIRST
  • Sort Method: quicksort Memory: 38kB
51. 0.063 252.625 ↑ 307.6 51 1

GroupAggregate (cost=14,231.53..15,957.05 rows=15,688 width=266) (actual time=252.568..252.625 rows=51 loops=1)

  • Group Key: cte0.c1, cte0.c2, cte0.c3, cte0.c4, cte0.c5, cte0.c6, cte0.c7, cte0.c8, cte0.c9, cte0.c10, cte0.c11, cte0.c12, cte0.c13, cte0.c14
52. 0.070 252.562 ↑ 754.2 52 1

Sort (cost=14,231.53..14,329.57 rows=39,216 width=262) (actual time=252.559..252.562 rows=52 loops=1)

  • Sort Key: cte0.c1, cte0.c2, cte0.c3 NULLS FIRST, cte0.c4, cte0.c5, cte0.c6, cte0.c7, cte0.c8, cte0.c9, cte0.c10, cte0.c11, cte0.c12, cte0.c13, cte0.c14
  • Sort Method: quicksort Memory: 38kB
53. 0.051 252.492 ↑ 754.2 52 1

Hash Left Join (cost=5,036.08..6,413.01 rows=39,216 width=262) (actual time=4.256..252.492 rows=52 loops=1)

  • Hash Cond: (cte0.c1 = t12298_meetingactivityaccountfact.c67681_accountid)
54. 250.049 250.049 ↑ 768.9 51 1

CTE Scan on filtered_accounts_cte cte0 (cost=0.00..784.32 rows=39,216 width=258) (actual time=1.858..250.049 rows=51 loops=1)

55. 0.019 2.392 ↓ 9.8 98 1

Hash (cost=5,035.96..5,035.96 rows=10 width=8) (actual time=2.392..2.392 rows=98 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
56. 0.127 2.373 ↓ 9.8 98 1

Bitmap Heap Scan on t12298_meetingactivityaccountfact (cost=2,519.36..5,035.96 rows=10 width=8) (actual time=2.256..2.373 rows=98 loops=1)

  • Recheck Cond: ((c67683_activitydate >= '1602226800000'::bigint) AND (c67683_activitydate < '1602918000000'::bigint) AND (start_stamp <= '1602861867000'::bigint) AND (end_stamp > '1602861867000'::bigint) AND c67687_isshared)
  • Filter: ((NOT deleted) AND (c67678_activityownersfuserid = c67691_accountid_ownerid))
  • Rows Removed by Filter: 300
  • Heap Blocks: exact=31
57. 2.246 2.246 ↑ 3.1 643 1

Bitmap Index Scan on idx_8405_t12298_meetingactivityaccountfact_c67683_activitydate_ (cost=0.00..2,519.35 rows=1,986 width=0) (actual time=2.246..2.246 rows=643 loops=1)

  • Index Cond: ((c67683_activitydate >= '1602226800000'::bigint) AND (c67683_activitydate < '1602918000000'::bigint) AND (start_stamp <= '1602861867000'::bigint) AND (end_stamp > '1602861867000'::bigint))
Planning time : 7.139 ms
Execution time : 253.327 ms