explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LNMF

Settings
# exclusive inclusive rows x rows loops node
1. 0.077 51,325.261 ↑ 1.0 1 1

Result (cost=7,377,345.89..7,377,345.90 rows=1 width=32) (actual time=51,325.260..51,325.261 rows=1 loops=1)

2.          

Initplan (forResult)

3. 0.034 51,325.184 ↑ 1.0 25 1

Limit (cost=7,377,345.83..7,377,345.89 rows=25 width=64) (actual time=51,325.142..51,325.184 rows=25 loops=1)

4. 1,021.017 51,325.150 ↑ 6,772.1 25 1

Sort (cost=7,377,345.83..7,377,769.08 rows=169,302 width=64) (actual time=51,325.140..51,325.150 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,729.304 50,304.133 ↓ 9.9 1,674,623 1

Subquery Scan on d (cost=7,365,372.91..7,372,568.25 rows=169,302 width=64) (actual time=32,040.057..50,304.133 rows=1,674,623 loops=1)

6. 1,551.790 37,574.829 ↓ 9.9 1,674,623 1

Unique (cost=7,365,372.91..7,369,605.46 rows=169,302 width=198) (actual time=32,040.031..37,574.829 rows=1,674,623 loops=1)

7. 10,774.050 36,023.039 ↓ 9.9 1,676,510 1

Sort (cost=7,365,372.91..7,365,796.17 rows=169,302 width=198) (actual time=32,040.028..36,023.039 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: external merge Disk: 230128kB
8. 5,540.363 25,248.989 ↓ 9.9 1,676,510 1

Hash Right Join (cost=7,306,663.63..7,334,464.68 rows=169,302 width=198) (actual time=19,685.398..25,248.989 rows=1,676,510 loops=1)

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

10. 2,027.769 19,684.712 ↓ 9.9 1,674,743 1

Hash (cost=7,290,824.36..7,290,824.36 rows=169,302 width=634) (actual time=19,684.711..19,684.712 rows=1,674,743 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 128 (originally 8) Memory Usage: 14081kB
11. 4,402.771 17,656.943 ↓ 9.9 1,674,743 1

Hash Join (cost=7,701.13..7,290,824.36 rows=169,302 width=634) (actual time=5,268.536..17,656.943 rows=1,674,743 loops=1)

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

Result (cost=0.00..5,145,563.32 rows=169,311,600 width=682) (actual time=0.032..7,985.996 rows=1,747,677 loops=1)

13. 2,992.860 5,553.109 ↑ 96.9 1,747,677 1

ProjectSet (cost=0.00..1,336,052.32 rows=169,311,600 width=666) (actual time=0.028..5,553.109 rows=1,747,677 loops=1)

14. 2,560.249 2,560.249 ↓ 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.013..2,560.249 rows=1,693,251 loops=1)

15. 1,087.478 5,268.176 ↓ 967.3 1,910,333 1

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

  • Buckets: 262144 (originally 2048) Batches: 8 (originally 1) Memory Usage: 13213kB
16. 3,870.400 4,180.698 ↓ 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=332.733..4,180.698 rows=1,910,333 loops=1)

  • Recheck Cond: (data @> jsonb_build_object('profile', 'CLIENT'))
  • Rows Removed by Index Recheck: 27476
  • Heap Blocks: exact=119631 lossy=230943
17. 310.298 310.298 ↓ 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=310.297..310.298 rows=1,910,517 loops=1)

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