explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BIQk

Settings
# exclusive inclusive rows x rows loops node
1. 38.089 3,386.376 ↑ 1.7 38,408 1

HashAggregate (cost=231,234.51..231,878.47 rows=64,396 width=4) (actual time=3,377.147..3,386.376 rows=38,408 loops=1)

  • Group Key: users.id
2. 6.106 3,348.287 ↑ 1.7 38,408 1

Append (cost=1,000.00..231,073.52 rows=64,396 width=4) (actual time=1.451..3,348.287 rows=38,408 loops=1)

3. 0.000 3,238.814 ↑ 1.7 38,408 1

Gather (cost=1,000.00..215,604.53 rows=64,329 width=4) (actual time=1.450..3,238.814 rows=38,408 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 3,244.922 3,244.922 ↑ 2.1 12,803 3

Parallel Seq Scan on users (cost=0.00..208,171.63 rows=26,804 width=4) (actual time=0.798..3,244.922 rows=12,803 loops=3)

  • Filter: ((deleted_at IS NULL) AND ((searchable @@ '''james'':*'::tsquery) OR (lower(email) = 'james'::text) OR (lower(email) ~~* '%james%@%'::text)))
  • Rows Removed by Filter: 1233762
5. 65.664 66.145 ↓ 0.0 0 1

Bitmap Heap Scan on user_clusters (cost=98.55..11,307.36 rows=59 width=4) (actual time=66.145..66.145 rows=0 loops=1)

  • Recheck Cond: ((cluster_id = 106) AND (deleted_at IS NULL))
  • Filter: ((searchable @@ to_tsquery('james:*'::text)) OR ((database_key)::text ~~* 'james'::text))
  • Rows Removed by Filter: 3459
  • Heap Blocks: exact=341
6. 0.481 0.481 ↓ 1.2 3,459 1

Bitmap Index Scan on user_clusters_user_id_hub_id_unique (cost=0.00..98.54 rows=2,948 width=0) (actual time=0.481..0.481 rows=3,459 loops=1)

  • Index Cond: (cluster_id = 106)
7. 0.001 0.592 ↓ 0.0 0 1

Bitmap Heap Scan on work_history (cost=68.53..72.79 rows=1 width=4) (actual time=0.592..0.592 rows=0 loops=1)

  • Recheck Cond: ((cluster_id = 106) AND (deleted_at IS NULL) AND (searchable @@ to_tsquery('james:*'::text)))
8. 0.025 0.591 ↓ 0.0 0 1

BitmapAnd (cost=68.53..68.53 rows=1 width=0) (actual time=0.591..0.591 rows=0 loops=1)

9. 0.085 0.085 ↑ 1.2 427 1

Bitmap Index Scan on unique_work_external_id_by_cluster (cost=0.00..32.30 rows=517 width=0) (actual time=0.085..0.085 rows=427 loops=1)

  • Index Cond: (cluster_id = 106)
10. 0.481 0.481 ↓ 1.4 711 1

Bitmap Index Scan on work_history_searchable_idx (cost=0.00..35.97 rows=497 width=0) (actual time=0.481..0.481 rows=711 loops=1)

  • Index Cond: (searchable @@ to_tsquery('james:*'::text))
11. 0.002 2.408 ↓ 0.0 0 1

Bitmap Heap Scan on user_schools (cost=95.82..100.09 rows=1 width=4) (actual time=2.408..2.408 rows=0 loops=1)

  • Recheck Cond: ((cluster_id = 106) AND (deleted_at IS NULL) AND (searchable @@ to_tsquery('james:*'::text)))
12. 0.010 2.406 ↓ 0.0 0 1

BitmapAnd (cost=95.82..95.82 rows=1 width=0) (actual time=2.406..2.406 rows=0 loops=1)

13. 1.010 1.010 ↑ 2.9 105 1

Bitmap Index Scan on unique_school_external_id_by_cluster (cost=0.00..10.68 rows=300 width=0) (actual time=1.010..1.010 rows=105 loops=1)

  • Index Cond: (cluster_id = 106)
14. 1.386 1.386 ↑ 2.6 240 1

Bitmap Index Scan on user_schools_searchable_idx (cost=0.00..84.89 rows=619 width=0) (actual time=1.385..1.386 rows=240 loops=1)

  • Index Cond: (searchable @@ to_tsquery('james:*'::text))
15. 0.237 34.222 ↓ 0.0 0 1

Nested Loop (cost=0.72..3,022.81 rows=6 width=4) (actual time=34.222..34.222 rows=0 loops=1)

16. 22.015 22.015 ↑ 2.9 105 1

Index Scan using unique_school_external_id_by_cluster on user_schools user_schools_1 (cost=0.43..1,171.53 rows=300 width=8) (actual time=0.303..22.015 rows=105 loops=1)

  • Index Cond: (cluster_id = 106)
17. 11.970 11.970 ↓ 0.0 0 105

Index Scan using schools_pkey on schools (cost=0.29..6.17 rows=1 width=4) (actual time=0.114..0.114 rows=0 loops=105)

  • Index Cond: (id = user_schools_1.school_id)
  • Filter: (to_tsvector('simple'::regconfig, name) @@ '''james'':*'::tsquery)
  • Rows Removed by Filter: 1