explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aleF

Settings
# exclusive inclusive rows x rows loops node
1. 0.076 47,677.604 ↑ 1.0 1 1

Result (cost=3,584,820.03..3,584,820.04 rows=1 width=32) (actual time=47,677.603..47,677.604 rows=1 loops=1)

2.          

Initplan (forResult)

3. 0.033 47,677.528 ↑ 1.0 25 1

Limit (cost=3,584,819.97..3,584,820.03 rows=25 width=64) (actual time=47,677.488..47,677.528 rows=25 loops=1)

4. 1,012.912 47,677.495 ↑ 6,772.1 25 1

Sort (cost=3,584,819.97..3,585,243.22 rows=169,302 width=64) (actual time=47,677.486..47,677.495 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,630.439 46,664.583 ↓ 9.9 1,674,623 1

Subquery Scan on d (cost=3,572,847.05..3,580,042.39 rows=169,302 width=64) (actual time=31,251.534..46,664.583 rows=1,674,623 loops=1)

6. 1,816.014 34,034.144 ↓ 9.9 1,674,623 1

Unique (cost=3,572,847.05..3,577,079.60 rows=169,302 width=198) (actual time=31,251.504..34,034.144 rows=1,674,623 loops=1)

7. 10,909.630 32,218.130 ↓ 9.9 1,676,510 1

Sort (cost=3,572,847.05..3,573,270.31 rows=169,302 width=198) (actual time=31,251.502..32,218.130 rows=1,676,510 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: quicksort Memory: 405784kB
8. 4,344.445 21,308.500 ↓ 9.9 1,676,510 1

Hash Right Join (cost=3,546,921.77..3,558,143.82 rows=169,302 width=198) (actual time=16,943.492..21,308.500 rows=1,676,510 loops=1)

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

Seq Scan on cm_certificated_specialist cs (cost=0.00..2,097.48 rows=47,148 width=222) (actual time=0.007..20.970 rows=47,648 loops=1)

10. 2,095.061 16,943.085 ↓ 9.9 1,674,743 1

Hash (cost=3,544,805.50..3,544,805.50 rows=169,302 width=634) (actual time=16,943.084..16,943.085 rows=1,674,743 loops=1)

  • Buckets: 1048576 (originally 262144) Batches: 2 (originally 1) Memory Usage: 606209kB
11. 3,811.242 14,848.024 ↓ 9.9 1,674,743 1

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

  • Hash Cond: ((((jsonb_array_elements((cm_user.data -> 'subscribers'::text))) ->> 'subscriberUeid'::text))::uuid = s.ueid)
12. 2,938.597 5,504.168 ↑ 96.9 1,747,677 1

ProjectSet (cost=0.00..1,336,052.32 rows=169,311,600 width=698) (actual time=0.016..5,504.168 rows=1,747,677 loops=1)

13. 2,565.571 2,565.571 ↓ 1.0 1,693,251 1

Seq Scan on cm_user (cost=0.00..472,563.16 rows=1,693,116 width=634) (actual time=0.007..2,565.571 rows=1,693,251 loops=1)

14. 1,043.919 5,532.614 ↓ 967.3 1,910,333 1

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

  • Buckets: 2097152 (originally 2048) Batches: 1 (originally 1) Memory Usage: 105931kB
15. 4,158.542 4,488.695 ↓ 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=400.862..4,488.695 rows=1,910,333 loops=1)

  • Recheck Cond: (data @> jsonb_build_object('profile', 'CLIENT'))
  • Heap Blocks: exact=350574
16. 330.153 330.153 ↓ 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=330.153..330.153 rows=1,910,517 loops=1)

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