explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LyOj

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 201,007.560 ↑ 1.0 20 1

Limit (cost=0.00..67,451,872.80 rows=20 width=102) (actual time=10,631.307..201,007.560 rows=20 loops=1)

2. 15.186 201,007.526 ↑ 4,691.5 20 1

Seq Scan on accounts a (cost=0.00..316,450,461,232.05 rows=93,830 width=102) (actual time=10,631.306..201,007.526 rows=20 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: 122
3.          

SubPlan (for Seq Scan)

4. 13.120 199,868.340 ↑ 1.0 1 20

Aggregate (cost=264,858.78..264,858.80 rows=1 width=32) (actual time=9,993.417..9,993.417 rows=1 loops=20)

5. 47,756.498 199,855.220 ↑ 6,866.0 2 20

Hash Join (cost=38,016.91..264,824.45 rows=13,732 width=8) (actual time=4,423.514..9,992.761 rows=2 loops=20)

  • Hash Cond: (s.uid = d.uid)
6. 151,647.702 151,647.702 ↓ 1.0 2,746,807 19

Seq Scan on sessions s (cost=0.00..219,598.35 rows=2,746,353 width=41) (actual time=0.007..7,981.458 rows=2,746,807 loops=19)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 3400164
7. 0.300 451.020 ↑ 13,730.0 1 20

Hash (cost=37,845.28..37,845.28 rows=13,730 width=41) (actual time=22.551..22.551 rows=1 loops=20)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
8. 295.460 450.720 ↑ 13,730.0 1 20

Bitmap Heap Scan on devices d (cost=258.84..37,845.28 rows=13,730 width=41) (actual time=16.482..22.536 rows=1 loops=20)

  • Recheck Cond: (a.id = account_id)
  • Heap Blocks: exact=29
9. 155.260 155.260 ↑ 13,730.0 1 20

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

  • Index Cond: (a.id = account_id)
10. 1.679 1,124.000 ↑ 1.0 1 20

Aggregate (cost=3,107,732.86..3,107,732.87 rows=1 width=32) (actual time=56.200..56.200 rows=1 loops=20)

11. 0.520 0.920 ↑ 13,730.0 1 20

Bitmap Heap Scan on devices d_1 (cost=258.84..37,845.28 rows=13,730 width=210) (actual time=0.040..0.046 rows=1 loops=20)

  • Recheck Cond: (a.id = account_id)
  • Heap Blocks: exact=29
12. 0.400 0.400 ↑ 13,730.0 1 20

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

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

SubPlan (for Aggregate)

14. 1.102 649.861 ↑ 1.0 1 29

Aggregate (cost=215.12..215.13 rows=1 width=32) (actual time=22.408..22.409 rows=1 loops=29)

15. 0.319 648.759 ↑ 53.0 1 29

Bitmap Heap Scan on sessions (cost=5.10..214.85 rows=53 width=51) (actual time=22.370..22.371 rows=1 loops=29)

  • Recheck Cond: ((uid = d_1.uid) AND (deleted_at IS NULL))
  • Heap Blocks: exact=31
16. 648.440 648.440 ↑ 53.0 1 29

Bitmap Index Scan on sessions_uid_deleted_at_idx (cost=0.00..5.09 rows=53 width=0) (actual time=22.360..22.360 rows=1 loops=29)

  • Index Cond: ((uid = d_1.uid) AND (deleted_at IS NULL))
17. 471.540 471.540 ↑ 1.0 1 29

Index Scan using pk_profiles on profiles (cost=0.43..8.45 rows=1 width=32) (actual time=16.258..16.260 rows=1 loops=29)

  • Index Cond: (id = d_1.current_profile_id)
Planning time : 39.209 ms