explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0JVV

Settings
# exclusive inclusive rows x rows loops node
1. 6,955.205 451,247.315 ↑ 1.1 82,185 1

Seq Scan on accounts a (cost=0.00..473,921,601,666.79 rows=93,830 width=102) (actual time=12.935..451,247.315 rows=82,185 loops=1)

  • Filter: ((deleted_at IS NULL) AND (updated_at > '1970-01-01 00:00:00'::timestamp without time zone) AND (type_name = 'personal'::account_type_enum) AND (home_mrf = 'mos'::text))
  • Rows Removed by Filter: 3171915
2.          

SubPlan (for Seq Scan)

3. 904.035 218,776.470 ↑ 1.0 1 82,185

Aggregate (cost=1,943,118.83..1,943,118.84 rows=1 width=32) (actual time=2.662..2.662 rows=1 loops=82,185)

4.          

CTE devices

5. 180,889.185 189,272.055 ↑ 13,730.0 1 82,185

Bitmap Heap Scan on devices d (cost=258.84..37,845.28 rows=13,730 width=12) (actual time=1.704..2.303 rows=1 loops=82,185)

  • Recheck Cond: (account_id = a.id)
  • Heap Blocks: exact=112462
6. 8,382.870 8,382.870 ↑ 13,730.0 1 82,185

Bitmap Index Scan on devices_accounts_id_idx (cost=0.00..255.41 rows=13,730 width=0) (actual time=0.102..0.102 rows=1 loops=82,185)

  • Index Cond: (account_id = a.id)
7.          

CTE sessions

8. 582.595 25,564.410 ↑ 686,588.0 2 76,770

Nested Loop (cost=309.36..86,834.85 rows=1,373,176 width=12) (actual time=0.245..0.333 rows=2 loops=76,770)

9. 460.620 537.390 ↑ 200.0 1 76,770

HashAggregate (cost=308.93..310.93 rows=200 width=8) (actual time=0.006..0.007 rows=1 loops=76,770)

  • Group Key: d_1.id
10. 76.770 76.770 ↑ 13,730.0 1 76,770

CTE Scan on devices d_1 (cost=0.00..274.60 rows=13,730 width=8) (actual time=0.000..0.001 rows=1 loops=76,770)

11. 24,444.425 24,444.425 ↑ 53.0 1 113,695

Index Scan using sessions_device_id_idx on sessions s (cost=0.43..432.09 rows=53 width=12) (actual time=0.210..0.215 rows=1 loops=113,695)

  • Index Cond: (device_id = d_1.id)
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 2
12. 717.585 217,872.435 ↑ 94,268,532.0 1 82,185

Merge Join (cost=168,670.73..1,582,767.36 rows=94,268,532 width=8) (actual time=2.649..2.651 rows=1 loops=82,185)

  • Merge Cond: (d_2.id = s_1.device_id)
13. 821.850 190,669.200 ↑ 13,730.0 1 82,185

Sort (cost=1,218.20..1,252.52 rows=13,730 width=12) (actual time=2.320..2.320 rows=1 loops=82,185)

  • Sort Key: d_2.id
  • Sort Method: quicksort Memory: 25kB
14. 189,847.350 189,847.350 ↑ 13,730.0 1 82,185

CTE Scan on devices d_2 (cost=0.00..274.60 rows=13,730 width=12) (actual time=1.710..2.310 rows=1 loops=82,185)

15. 690.930 26,485.650 ↑ 686,588.0 2 76,770

Sort (cost=167,452.53..170,885.47 rows=1,373,176 width=12) (actual time=0.345..0.345 rows=2 loops=76,770)

  • Sort Key: s_1.device_id
  • Sort Method: quicksort Memory: 25kB
16. 25,794.720 25,794.720 ↑ 686,588.0 2 76,770

CTE Scan on sessions s_1 (cost=0.00..27,463.52 rows=1,373,176 width=12) (actual time=0.248..0.336 rows=2 loops=76,770)

17. 3,146.090 225,515.640 ↑ 1.0 1 82,185

Aggregate (cost=3,107,732.86..3,107,732.87 rows=1 width=32) (actual time=2.744..2.744 rows=1 loops=82,185)

18. 575.295 1,232.775 ↑ 13,730.0 1 82,185

Bitmap Heap Scan on devices d_3 (cost=258.84..37,845.28 rows=13,730 width=210) (actual time=0.013..0.015 rows=1 loops=82,185)

  • Recheck Cond: (a.id = account_id)
  • Heap Blocks: exact=112462
19. 657.480 657.480 ↑ 13,730.0 1 82,185

Bitmap Index Scan on devices_accounts_id_idx (cost=0.00..255.41 rows=13,730 width=0) (actual time=0.008..0.008 rows=1 loops=82,185)

  • Index Cond: (a.id = account_id)
20.          

SubPlan (for Aggregate)

21. 1,705.425 88,227.320 ↑ 1.0 1 113,695

Aggregate (cost=215.12..215.13 rows=1 width=32) (actual time=0.776..0.776 rows=1 loops=113,695)

22. 454.780 86,521.895 ↑ 53.0 1 113,695

Bitmap Heap Scan on sessions (cost=5.10..214.85 rows=53 width=51) (actual time=0.760..0.761 rows=1 loops=113,695)

  • Recheck Cond: ((uid = d_3.uid) AND (deleted_at IS NULL))
  • Heap Blocks: exact=120813
23. 86,067.115 86,067.115 ↑ 53.0 1 113,695

Bitmap Index Scan on sessions_uid_deleted_at_idx (cost=0.00..5.09 rows=53 width=0) (actual time=0.757..0.757 rows=1 loops=113,695)

  • Index Cond: ((uid = d_3.uid) AND (deleted_at IS NULL))
24. 132,909.455 132,909.455 ↑ 1.0 1 113,695

Index Scan using pk_profiles on profiles (cost=0.43..8.45 rows=1 width=32) (actual time=1.167..1.169 rows=1 loops=113,695)

  • Index Cond: (id = d_3.current_profile_id)
Planning time : 0.779 ms