explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0sG

Settings
# exclusive inclusive rows x rows loops node
1. 23.795 82,102.457 ↑ 1.0 1 1

Aggregate (cost=4,523,352.15..4,523,352.16 rows=1 width=8) (actual time=82,102.456..82,102.457 rows=1 loops=1)

2. 122.315 82,078.662 ↑ 1,078.3 7,389 1

Nested Loop (cost=7,701.50..4,503,433.34 rows=7,967,521 width=35) (actual time=5,693.160..82,078.662 rows=7,389 loops=1)

3. 2,217.275 14,753.429 ↑ 3.4 50,377 1

Hash Join (cost=7,701.13..3,544,805.50 rows=169,302 width=35) (actual time=5,685.714..14,753.429 rows=50,377 loops=1)

  • Hash Cond: ((((jsonb_array_elements((cm_user.data -> 'subscribers'::text))) ->> 'subscriberUeid'::text))::uuid = s.ueid)
4. 3,095.639 6,851.918 ↑ 96.9 1,747,660 1

ProjectSet (cost=0.00..1,336,052.32 rows=169,311,600 width=99) (actual time=0.015..6,851.918 rows=1,747,660 loops=1)

5. 3,756.279 3,756.279 ↓ 1.0 1,693,243 1

Seq Scan on cm_user (cost=0.00..472,563.16 rows=1,693,116 width=634) (actual time=0.006..3,756.279 rows=1,693,243 loops=1)

6. 39.573 5,684.236 ↓ 28.0 55,396 1

Hash (cost=7,676.44..7,676.44 rows=1,975 width=16) (actual time=5,684.235..5,684.236 rows=55,396 loops=1)

  • Buckets: 65536 (originally 2048) Batches: 1 (originally 1) Memory Usage: 3109kB
7. 5,327.516 5,644.663 ↓ 28.0 55,396 1

Bitmap Heap Scan on cm_subscriber s (cost=67.31..7,676.44 rows=1,975 width=16) (actual time=321.394..5,644.663 rows=55,396 loops=1)

  • Recheck Cond: (data @> jsonb_build_object('profile', 'PARTNER_ORGANIZATION'))
  • Heap Blocks: exact=20174
8. 317.147 317.147 ↓ 28.0 55,396 1

Bitmap Index Scan on cm_subscriber_table_index (cost=0.00..66.82 rows=1,975 width=0) (actual time=317.147..317.147 rows=55,396 loops=1)

  • Index Cond: (data @> jsonb_build_object('profile', 'PARTNER_ORGANIZATION'))
9. 453.393 67,202.918 ↓ 0.0 0 50,377

Bitmap Heap Scan on cm_certificated_specialist cs (cost=0.37..5.19 rows=47 width=206) (actual time=1.333..1.334 rows=0 loops=50,377)

  • Recheck Cond: (data @> jsonb_strip_nulls(jsonb_build_object('userUeid', cm_user.ueid, 'isDirector', false, 'isResponsible', false)))
  • Rows Removed by Index Recheck: 0
  • Heap Blocks: exact=11902
10. 66,749.525 66,749.525 ↓ 0.0 0 50,377

Bitmap Index Scan on cm_certificated_specialist_table_index (cost=0.00..0.36 rows=47 width=0) (actual time=1.325..1.325 rows=0 loops=50,377)

  • Index Cond: (data @> jsonb_strip_nulls(jsonb_build_object('userUeid', cm_user.ueid, 'isDirector', false, 'isResponsible', false)))