explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MMg5

Settings
# exclusive inclusive rows x rows loops node
1. 0.082 67,480.832 ↑ 1.0 1 1

Result (cost=3,631,330.47..3,631,330.48 rows=1 width=32) (actual time=67,480.831..67,480.832 rows=1 loops=1)

2.          

Initplan (forResult)

3. 0.033 67,480.750 ↑ 1.0 25 1

Limit (cost=3,631,330.40..3,631,330.47 rows=25 width=64) (actual time=67,480.710..67,480.750 rows=25 loops=1)

4. 1,031.033 67,480.717 ↑ 6,772.1 25 1

Sort (cost=3,631,330.40..3,631,753.66 rows=169,302 width=64) (actual time=67,480.707..67,480.717 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,677.893 66,449.684 ↓ 9.9 1,674,623 1

Subquery Scan on d (cost=3,619,357.49..3,626,552.82 rows=169,302 width=64) (actual time=48,182.187..66,449.684 rows=1,674,623 loops=1)

6. 1,547.704 53,771.791 ↓ 9.9 1,674,623 1

Unique (cost=3,619,357.49..3,623,590.04 rows=169,302 width=198) (actual time=48,182.157..53,771.791 rows=1,674,623 loops=1)

7. 10,855.948 52,224.087 ↓ 9.9 1,676,512 1

Sort (cost=3,619,357.49..3,619,780.74 rows=169,302 width=198) (actual time=48,182.155..52,224.087 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. 7,272.062 41,368.139 ↓ 9.9 1,676,512 1

Hash Right Join (cost=3,560,644.77..3,588,449.25 rows=169,302 width=198) (actual time=34,072.552..41,368.139 rows=1,676,512 loops=1)

  • Hash Cond: ((cs.data ->> 'userUeid'::text) = (cm_user.ueid)::text)
9. 24.193 24.193 ↓ 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.020..24.193 rows=47,668 loops=1)

10. 1,985.626 34,071.884 ↓ 9.9 1,674,743 1

Hash (cost=3,544,805.50..3,544,805.50 rows=169,302 width=634) (actual time=34,071.883..34,071.884 rows=1,674,743 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 128 (originally 16) Memory Usage: 13057kB
11. 6,778.627 32,086.258 ↓ 9.9 1,674,743 1

Hash Join (cost=7,701.13..3,544,805.50 rows=169,302 width=634) (actual time=5,420.926..32,086.258 rows=1,674,743 loops=1)

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

ProjectSet (cost=0.00..1,336,052.32 rows=169,311,600 width=698) (actual time=0.018..19,886.939 rows=1,747,680 loops=1)

13. 16,775.943 16,775.943 ↓ 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.009..16,775.943 rows=1,693,253 loops=1)

14. 1,116.814 5,420.692 ↓ 967.3 1,910,333 1

Hash (cost=7,676.44..7,676.44 rows=1,975 width=16) (actual time=5,420.691..5,420.692 rows=1,910,333 loops=1)

  • Buckets: 262144 (originally 2048) Batches: 8 (originally 1) Memory Usage: 13213kB
15. 3,996.585 4,303.878 ↓ 967.3 1,910,333 1

Bitmap Heap Scan on cm_subscriber s (cost=67.31..7,676.44 rows=1,975 width=16) (actual time=341.154..4,303.878 rows=1,910,333 loops=1)

  • Recheck Cond: (data @> jsonb_build_object('profile', 'CLIENT'))
  • Rows Removed by Index Recheck: 27326
  • Heap Blocks: exact=136112 lossy=214462
16. 307.293 307.293 ↓ 967.4 1,910,517 1

Bitmap Index Scan on cm_subscriber_table_index (cost=0.00..66.82 rows=1,975 width=0) (actual time=307.292..307.293 rows=1,910,517 loops=1)

  • Index Cond: (data @> jsonb_build_object('profile', 'CLIENT'))