explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RSML

Settings
# exclusive inclusive rows x rows loops node
1. 6.032 75,165.541 ↓ 844.0 844 1

HashAggregate (cost=695.70..695.71 rows=1 width=40) (actual time=75,165.234..75,165.541 rows=844 loops=1)

  • Group Key: all_users.account_did, all_users.user_did, all_users.user_group_id, all_users.ugu_start_date, all_users.ugu_end_date
2.          

CTE all_users

3. 76.421 1,676.705 ↓ 142,838.0 142,838 1

Unique (cost=695.61..695.63 rows=1 width=40) (actual time=1,558.522..1,676.705 rows=142,838 loops=1)

4. 671.111 1,600.284 ↓ 142,838.0 142,838 1

Sort (cost=695.61..695.62 rows=1 width=40) (actual time=1,558.520..1,600.284 rows=142,838 loops=1)

  • Sort Key: start_date_dimension.date_time_value, end_date_dimension.date_time_value, account_dimension.account_did, user_group_user_dimension.user_did, user_group_dimension.user_group_id
  • Sort Method: external merge Disk: 5880kB
5. 107.906 929.173 ↓ 142,838.0 142,838 1

Nested Loop (cost=1.87..695.60 rows=1 width=40) (actual time=0.175..929.173 rows=142,838 loops=1)

6. 124.414 535.591 ↓ 142,838.0 142,838 1

Nested Loop (cost=1.57..695.29 rows=1 width=40) (actual time=0.133..535.591 rows=142,838 loops=1)

7. 67.445 125.501 ↓ 142,838.0 142,838 1

Nested Loop (cost=1.28..694.97 rows=1 width=40) (actual time=0.118..125.501 rows=142,838 loops=1)

8. 0.003 0.086 ↓ 2.0 2 1

Nested Loop (cost=0.71..21.32 rows=1 width=24) (actual time=0.073..0.086 rows=2 loops=1)

  • Join Filter: (user_group_dimension.account_did = account_dimension.account_did)
9. 0.031 0.031 ↑ 1.0 1 1

Index Scan using account_id_unique on account_dimension (cost=0.28..8.29 rows=1 width=8) (actual time=0.029..0.031 rows=1 loops=1)

  • Index Cond: (account_id = 478)
10. 0.052 0.052 ↑ 1.0 2 1

Index Scan using user_group_dimension_unique on user_group_dimension (cost=0.43..13.00 rows=2 width=24) (actual time=0.041..0.052 rows=2 loops=1)

  • Index Cond: (user_group_id = ANY ('{1907716,1879687}'::bigint[]))
11. 57.970 57.970 ↓ 5.8 71,419 2

Index Only Scan using user_group_user_dimension_unique on user_group_user_dimension (cost=0.57..549.62 rows=12,403 width=32) (actual time=0.028..28.985 rows=71,419 loops=2)

  • Index Cond: (user_group_did = user_group_dimension.user_group_did)
  • Heap Fetches: 0
12. 285.676 285.676 ↑ 1.0 1 142,838

Index Scan using date_time_dimension_primary on date_time_dimension start_date_dimension (cost=0.30..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=142,838)

  • Index Cond: (date_time_did = user_group_user_dimension.start_date_did)
  • Filter: (date_time_value <= '2019-10-30 09:31:31.183'::timestamp without time zone)
13. 285.676 285.676 ↑ 1.0 1 142,838

Index Scan using date_time_dimension_primary on date_time_dimension end_date_dimension (cost=0.30..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=142,838)

  • Index Cond: (date_time_did = user_group_user_dimension.end_date_did)
  • Filter: ((date_time_value >= '2019-01-01 00:00:00'::timestamp without time zone) OR (date_time_value IS NULL))
14. 28,495.309 75,159.509 ↓ 844.0 844 1

Nested Loop (cost=0.00..0.06 rows=1 width=40) (actual time=1,734.623..75,159.509 rows=844 loops=1)

  • Join Filter: (all_users.user_did = top_group_1879687.user_did)
  • Rows Removed by Join Filter: 119842092
15. 1,584.472 1,584.472 ↓ 844.0 844 1

CTE Scan on all_users (cost=0.00..0.02 rows=1 width=40) (actual time=1,558.529..1,584.472 rows=844 loops=1)

  • Filter: (user_group_id <> 1879687)
  • Rows Removed by Filter: 141994
16. 45,079.728 45,079.728 ↓ 141,994.0 141,994 844

CTE Scan on all_users top_group_1879687 (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..53.412 rows=141,994 loops=844)

  • Filter: (user_group_id = 1879687)
  • Rows Removed by Filter: 844
Planning time : 2.742 ms
Execution time : 75,168.121 ms