explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qd51

Settings
# exclusive inclusive rows x rows loops node
1. 37.006 4,466.835 ↑ 1.8 38,515 1

HashAggregate (cost=710,824.05..711,521.85 rows=69,780 width=4) (actual time=4,456.491..4,466.835 rows=38,515 loops=1)

  • Group Key: users.id
2. 6.331 4,429.829 ↑ 1.8 38,527 1

Append (cost=1,000.00..710,649.60 rows=69,780 width=4) (actual time=1.994..4,429.829 rows=38,527 loops=1)

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

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

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

Parallel Seq Scan on users (cost=0.00..208,171.63 rows=26,804 width=4) (actual time=0.983..3,359.426 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. 6.200 205.239 ↓ 0.0 0 1

Gather (cost=1,000.43..365,873.17 rows=2,724 width=4) (actual time=205.222..205.239 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 199.039 199.039 ↓ 0.0 0 3

Parallel Index Scan using user_clusters_user_id_hub_id_unique on user_clusters (cost=0.43..364,600.77 rows=1,135 width=4) (actual time=199.039..199.039 rows=0 loops=3)

  • Index Cond: (cluster_id = 92)
  • Filter: ((searchable @@ to_tsquery('james:*'::text)) OR ((database_key)::text ~~* 'james'::text))
  • Rows Removed by Filter: 45561
7. 57.753 91.302 ↓ 0.0 0 1

Bitmap Heap Scan on user_hubs (cost=1,603.29..3,275.13 rows=14 width=4) (actual time=91.302..91.302 rows=0 loops=1)

  • Recheck Cond: (searchable_custom_fields @@ to_tsquery('james:*'::text))
  • Filter: ((deleted_at IS NULL) AND (hub_id = 92))
  • Rows Removed by Filter: 351
  • Heap Blocks: exact=304
8. 33.549 33.549 ↑ 1.2 351 1

Bitmap Index Scan on textsearch_idx (cost=0.00..1,603.29 rows=405 width=0) (actual time=33.549..33.549 rows=351 loops=1)

  • Index Cond: (searchable_custom_fields @@ to_tsquery('james:*'::text))
9. 0.821 2.495 ↓ 2.5 115 1

Bitmap Heap Scan on work_history (cost=35.99..1,992.93 rows=46 width=4) (actual time=1.783..2.495 rows=115 loops=1)

  • Recheck Cond: (searchable @@ to_tsquery('james:*'::text))
  • Filter: ((deleted_at IS NULL) AND (cluster_id = 92))
  • Rows Removed by Filter: 596
  • Heap Blocks: exact=683
10. 1.674 1.674 ↓ 1.4 711 1

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

  • Index Cond: (searchable @@ to_tsquery('james:*'::text))
11. 0.260 0.463 ↑ 12.0 2 1

Bitmap Heap Scan on user_schools (cost=84.90..2,567.36 rows=24 width=4) (actual time=0.402..0.463 rows=2 loops=1)

  • Recheck Cond: (searchable @@ to_tsquery('james:*'::text))
  • Filter: ((deleted_at IS NULL) AND (cluster_id = 92))
  • Rows Removed by Filter: 238
  • Heap Blocks: exact=235
12. 0.203 0.203 ↑ 2.6 240 1

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

  • Index Cond: (searchable @@ to_tsquery('james:*'::text))
13. 4.817 767.645 ↑ 1,321.5 2 1

Gather (cost=7,812.30..120,289.79 rows=2,643 width=4) (actual time=767.524..767.645 rows=2 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 8.627 762.828 ↑ 1,101.0 1 3

Hash Join (cost=6,812.30..119,025.49 rows=1,101 width=4) (actual time=688.186..762.828 rows=1 loops=3)

  • Hash Cond: (user_schools_1.school_id = schools.id)
15. 624.160 624.160 ↑ 1.1 50,989 3

Parallel Seq Scan on user_schools user_schools_1 (cost=0.00..112,068.56 rows=55,088 width=8) (actual time=0.063..624.160 rows=50,989 loops=3)

  • Filter: ((deleted_at IS NULL) AND (cluster_id = 92))
  • Rows Removed by Filter: 1073995
16. 0.042 130.041 ↑ 20.3 22 3

Hash (cost=6,806.71..6,806.71 rows=447 width=4) (actual time=130.041..130.041 rows=22 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 129.999 129.999 ↑ 20.3 22 3

Seq Scan on schools (cost=0.00..6,806.71 rows=447 width=4) (actual time=8.045..129.999 rows=22 loops=3)

  • Filter: (to_tsvector('simple'::regconfig, name) @@ '''james'':*'::tsquery)
  • Rows Removed by Filter: 22335
Planning time : 7.145 ms
Execution time : 4,469.907 ms