explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HRGV

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 108,691.144 ↑ 1.0 20 1

Limit (cost=0.00..67,451,872.80 rows=20 width=102) (actual time=7,449.182..108,691.144 rows=20 loops=1)

2. 6.933 108,691.113 ↑ 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=7,449.180..108,691.113 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: 2,117
3.          

SubPlan (for Seq Scan)

4. 1.860 108,585.740 ↑ 1.0 1 20

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

5. 26,205.437 108,583.880 ↑ 13,732.0 1 20

Hash Join (cost=38,016.91..264,824.45 rows=13,732 width=8) (actual time=3,540.870..5,429.194 rows=1 loops=20)

  • Hash Cond: (s.uid = d.uid)
6. 82,312.283 82,312.283 ↓ 1.0 2,746,807 17

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

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 3,400,164
7. 0.260 66.160 ↑ 13,730.0 1 20

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 129kB
8. 26.320 65.900 ↑ 13,730.0 1 20

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

  • Recheck Cond: (a.id = account_id)
  • Heap Blocks: exact=28
9. 39.580 39.580 ↑ 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=1.979..1.979 rows=1 loops=20)

  • Index Cond: (a.id = account_id)
10. 1.020 98.440 ↑ 1.0 1 20

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

11. 0.280 1.380 ↑ 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.064..0.069 rows=1 loops=20)

  • Recheck Cond: (a.id = account_id)
  • Heap Blocks: exact=28
12. 1.100 1.100 ↑ 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.055..0.055 rows=1 loops=20)

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

SubPlan (for Aggregate)

14. 0.560 1.680 ↑ 1.0 1 28

Aggregate (cost=215.12..215.13 rows=1 width=32) (actual time=0.060..0.060 rows=1 loops=28)

15. 0.140 1.120 ↑ 53.0 1 28

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

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

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

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

Index Scan using pk_profiles on profiles (cost=0.43..8.45 rows=1 width=32) (actual time=3.369..3.370 rows=1 loops=28)

  • Index Cond: (id = d_1.current_profile_id)