explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b1G8

Settings
# exclusive inclusive rows x rows loops node
1. 0.110 71,377.599 ↑ 1.0 1 1

Result (cost=4,681,136.51..4,681,136.53 rows=1 width=32) (actual time=71,377.598..71,377.599 rows=1 loops=1)

2.          

Initplan (forResult)

3. 0.171 71,377.489 ↑ 1.0 25 1

Limit (cost=4,681,136.45..4,681,136.51 rows=25 width=64) (actual time=71,377.311..71,377.489 rows=25 loops=1)

4. 1,037.864 71,377.318 ↑ 33,863.9 25 1

Sort (cost=4,681,136.45..4,683,252.94 rows=846,597 width=64) (actual time=71,377.309..71,377.318 rows=25 loops=1)

  • Sort Key: (CASE WHEN (length(d.fullname) = 0) THEN NULL::text ELSE d.fullname END)
  • Sort Method: top-N heapsort Memory: 43kB
5. 12,761.107 70,339.454 ↓ 2.0 1,674,623 1

Subquery Scan on d (cost=4,621,265.72..4,657,246.09 rows=846,597 width=64) (actual time=51,966.916..70,339.454 rows=1,674,623 loops=1)

6. 1,552.277 57,578.347 ↓ 2.0 1,674,623 1

Unique (cost=4,621,265.72..4,642,430.64 rows=846,597 width=198) (actual time=51,966.889..57,578.347 rows=1,674,623 loops=1)

7. 10,966.238 56,026.070 ↓ 2.0 1,676,512 1

Sort (cost=4,621,265.72..4,623,382.21 rows=846,597 width=198) (actual time=51,966.886..56,026.070 rows=1,676,512 loops=1)

  • Sort Key: cm_user.ueid, ((cm_user.data ->> 'login'::text)), (btrim(((((COALESCE(((cm_user.data -> 'fullName'::text) ->> 'lastName'::text), ''::text) || ' '::text) || COALESCE(((cm_user.data -> 'fullName'::text) ->> 'firstName'::text), ''::text)) || ' '::text) || COALESCE(((cm_user.data -> 'fullName'::text) ->> 'middleName'::text), ''::text)))), ((cm_user.data #>> '{contact,email}'::text[])), ((cm_user.data ->> 'city'::text)), ((cm_user.data ->> 'firmName'::text)), ((cs.ueid IS NOT NULL)), (COALESCE(((cs.data ->> 'isResponsible'::text))::boolean, false)), (COALESCE(((cs.data ->> 'isDirector'::text))::boolean, false))
  • Sort Method: external merge Disk: 230160kB
8. 6,922.984 45,059.832 ↓ 2.0 1,676,512 1

Hash Right Join (cost=4,338,625.32..4,456,887.67 rows=846,597 width=198) (actual time=38,113.436..45,059.832 rows=1,676,512 loops=1)

  • Hash Cond: ((cs.data ->> 'userUeid'::text) = (cm_user.ueid)::text)
9. 24.017 24.017 ↓ 1.0 47,668 1

Seq Scan on cm_certificated_specialist cs (cost=0.00..2,098.77 rows=47,177 width=222) (actual time=0.018..24.017 rows=47,668 loops=1)

10. 1,989.828 38,112.831 ↓ 2.0 1,674,743 1

Hash (cost=4,259,421.85..4,259,421.85 rows=846,597 width=634) (actual time=38,112.830..38,112.831 rows=1,674,743 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 128 (originally 64) Memory Usage: 13057kB
11. 7,984.823 36,123.003 ↓ 2.0 1,674,743 1

Hash Join (cost=722,317.49..4,259,421.85 rows=846,597 width=634) (actual time=18,005.799..36,123.003 rows=1,674,743 loops=1)

  • Hash Cond: ((((jsonb_array_elements((cm_user.data -> 'subscribers'::text))) ->> 'subscriberUeid'::text))::uuid = s.ueid)
12. 3,087.203 10,132.665 ↑ 96.9 1,747,680 1

ProjectSet (cost=0.00..1,336,052.32 rows=169,311,600 width=698) (actual time=0.019..10,132.665 rows=1,747,680 loops=1)

13. 7,045.462 7,045.462 ↓ 1.0 1,693,253 1

Seq Scan on cm_user (cost=0.00..472,563.16 rows=1,693,116 width=634) (actual time=0.010..7,045.462 rows=1,693,253 loops=1)

14. 1,128.151 18,005.515 ↓ 193.4 1,910,333 1

Hash (cost=722,194.04..722,194.04 rows=9,876 width=16) (actual time=18,005.514..18,005.515 rows=1,910,333 loops=1)

  • Buckets: 262144 (originally 16384) Batches: 8 (originally 1) Memory Usage: 13213kB
15. 389.398 16,877.364 ↓ 193.4 1,910,333 1

Gather (cost=1,000.00..722,194.04 rows=9,876 width=16) (actual time=0.335..16,877.364 rows=1,910,333 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 16,487.966 16,487.966 ↓ 154.7 636,778 3

Parallel Seq Scan on cm_subscriber s (cost=0.00..720,206.44 rows=4,115 width=16) (actual time=15.426..16,487.966 rows=636,778 loops=3)

  • Filter: ((data ->> 'profile'::text) = 'CLIENT'::text)
  • Rows Removed by Filter: 21606