explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JOSs

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 110,741.279 ↑ 1.0 20 1

Limit (cost=0.00..67,451,872.80 rows=20 width=102) (actual time=7,219.549..110,741.279 rows=20 loops=1)

2. 3.007 110,741.247 ↑ 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,219.548..110,741.247 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: 1912
3.          

SubPlan (for Seq Scan)

4. 2.240 110,456.640 ↑ 1.0 1 20

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

5. 26,748.748 110,454.400 ↑ 13,732.0 1 20

Hash Join (cost=38,016.91..264,824.45 rows=13,732 width=8) (actual time=2,229.495..5,522.720 rows=1 loops=20)

  • Hash Cond: (s.uid = d.uid)
6. 83,577.672 83,577.672 ↓ 1.0 2,746,807 18

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

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

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

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

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

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

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

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

11. 0.240 0.580 ↑ 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.027..0.029 rows=1 loops=20)

  • Recheck Cond: (a.id = account_id)
  • Heap Blocks: exact=19
12. 0.340 0.340 ↑ 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.017..0.017 rows=1 loops=20)

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

SubPlan (for Aggregate)

14. 0.646 123.747 ↑ 1.0 1 19

Aggregate (cost=215.12..215.13 rows=1 width=32) (actual time=6.513..6.513 rows=1 loops=19)

15. 0.209 123.101 ↑ 53.0 1 19

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

  • Recheck Cond: ((uid = d_1.uid) AND (deleted_at IS NULL))
  • Heap Blocks: exact=22
16. 122.892 122.892 ↑ 53.0 1 19

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

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

Index Scan using pk_profiles on profiles (cost=0.43..8.45 rows=1 width=32) (actual time=8.205..8.208 rows=1 loops=19)

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