explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JDjh

Settings
# exclusive inclusive rows x rows loops node
1. 9,164.545 234,762.370 ↑ 1.1 82,185 1

Seq Scan on accounts a (cost=0.00..309,828,051,527.43 rows=93,830 width=102) (actual time=0.201..234,762.370 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. 1,232.775 74,048.685 ↑ 1.0 1 82,185

Aggregate (cost=194,279.97..194,279.98 rows=1 width=32) (actual time=0.901..0.901 rows=1 loops=82,185)

4.          

CTE devices

5. 64,515.225 66,569.850 ↑ 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=0.596..0.810 rows=1 loops=82,185)

  • Recheck Cond: (account_id = a.id)
  • Heap Blocks: exact=112462
6. 2,054.625 2,054.625 ↑ 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.025..0.025 rows=1 loops=82,185)

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

CTE sessions

8. 540.850 72,076.245 ↑ 768,454.5 4 82,185

Nested Loop (cost=309.36..86,964.85 rows=3,073,818 width=4) (actual time=0.855..0.877 rows=4 loops=82,185)

9. 575.295 67,556.070 ↑ 200.0 1 82,185

HashAggregate (cost=308.93..310.93 rows=200 width=8) (actual time=0.821..0.822 rows=1 loops=82,185)

  • Group Key: d_1.id
10. 66,980.775 66,980.775 ↑ 13,730.0 1 82,185

CTE Scan on devices d_1 (cost=0.00..274.60 rows=13,730 width=8) (actual time=0.600..0.815 rows=1 loops=82,185)

11. 3,979.325 3,979.325 ↑ 39.3 3 113,695

Index Scan using sessions_device_id_idx on sessions s (cost=0.43..432.09 rows=118 width=12) (actual time=0.029..0.035 rows=3 loops=113,695)

  • Index Cond: (device_id = d_1.id)
12. 246.555 72,815.910 ↑ 514,591.3 6 82,185

Append (cost=0.00..61,750.96 rows=3,087,548 width=4) (actual time=0.858..0.886 rows=6 loops=82,185)

13. 72,487.170 72,487.170 ↑ 768,454.5 4 82,185

CTE Scan on sessions s_1 (cost=0.00..61,476.36 rows=3,073,818 width=4) (actual time=0.857..0.882 rows=4 loops=82,185)

14. 82.185 82.185 ↑ 13,730.0 1 82,185

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

15. 2,904.625 151,549.140 ↑ 1.0 1 82,185

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

16. 493.110 1,068.405 ↑ 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.010..0.013 rows=1 loops=82,185)

  • Recheck Cond: (a.id = account_id)
  • Heap Blocks: exact=112462
17. 575.295 575.295 ↑ 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.007..0.007 rows=1 loops=82,185)

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

SubPlan (for Aggregate)

19. 1,591.730 45,705.390 ↑ 1.0 1 113,695

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

20. 341.085 44,113.660 ↑ 53.0 1 113,695

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

  • Recheck Cond: ((uid = d_3.uid) AND (deleted_at IS NULL))
  • Heap Blocks: exact=120813
21. 43,772.575 43,772.575 ↑ 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.385..0.385 rows=1 loops=113,695)

  • Index Cond: ((uid = d_3.uid) AND (deleted_at IS NULL))
22. 101,870.720 101,870.720 ↑ 1.0 1 113,695

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

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