explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zrpm

Settings
# exclusive inclusive rows x rows loops node
1. 1.983 1.983 ↓ 0.0 0 1

CTE Scan on replacements (cost=897,358.51..1,758,218.51 rows=43,043,000 width=112) (actual time=1.983..1.983 rows=0 loops=1)

2.          

CTE recip

3. 0.047 0.161 ↑ 8,681.0 1 1

Recursive Union (cost=0.43..33,956.08 rows=8,681 width=120) (actual time=0.078..0.161 rows=1 loops=1)

4. 0.032 0.032 ↑ 1.0 1 1

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r (cost=0.43..3.45 rows=1 width=120) (actual time=0.031..0.032 rows=1 loops=1)

  • Index Cond: (recipient_id = ANY ('{1947001}'::bigint[]))
  • Filter: (org_uuid = 'cbb40e91-1659-4cf6-8d9e-df215cc48d6a'::uuid)
5. 0.001 0.082 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.03..3,377.90 rows=868 width=120) (actual time=0.082..0.082 rows=0 loops=1)

  • Filter: (COALESCE(r_memofteam.recipient_id, r_memofgroup.recipient_id) IS NOT NULL)
  • Rows Removed by Filter: 1
6. 0.005 0.076 ↑ 872.0 1 1

Nested Loop Left Join (cost=3.60..1,475.12 rows=872 width=118) (actual time=0.075..0.076 rows=1 loops=1)

7. 0.001 0.049 ↑ 17.0 1 1

Nested Loop Left Join (cost=1.26..116.87 rows=17 width=102) (actual time=0.048..0.049 rows=1 loops=1)

8. 0.001 0.044 ↑ 17.0 1 1

Nested Loop Left Join (cost=0.84..80.14 rows=17 width=96) (actual time=0.043..0.044 rows=1 loops=1)

9. 0.003 0.038 ↑ 17.0 1 1

Nested Loop Left Join (cost=0.42..48.27 rows=17 width=96) (actual time=0.037..0.038 rows=1 loops=1)

10. 0.010 0.010 ↑ 10.0 1 1

WorkTable Scan on recip (cost=0.00..0.30 rows=10 width=72) (actual time=0.010..0.010 rows=1 loops=1)

  • Filter: (NOT (ARRAY[chain_ids[array_upper(chain_ids, 1)]] <@ chain_ids[1:(array_upper(chain_ids, 1) - 1)]))
11. 0.025 0.025 ↓ 0.0 0 1

Index Scan using idx_grp_tf1 on grp_tf gtf (cost=0.42..4.78 rows=2 width=24) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: (recip.recipient_id = grp_id)
  • Filter: (deleted_id = '-1'::integer)
12. 0.005 0.005 ↓ 0.0 0 1

Index Scan using tm_pk on tm t (cost=0.42..1.87 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (gtf.tm_id = tm_id)
  • Filter: ((deleted_id = '-1'::integer) AND (grp_id = gtf.grp_id))
13. 0.004 0.004 ↓ 0.0 0 1

Index Scan using recipients_recip_id_org_id_not_deleted_pidx on recipients r_memofgroup (cost=0.43..2.16 rows=1 width=14) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (t.tm_id = recipient_id)
14. 0.005 0.022 ↓ 0.0 0 1

Bitmap Heap Scan on tm_mbrships tm (cost=2.34..79.37 rows=53 width=24) (actual time=0.022..0.022 rows=0 loops=1)

  • Recheck Cond: (recip.recipient_id = tm_id)
15. 0.017 0.017 ↓ 0.0 0 1

Bitmap Index Scan on tm_mbrships_pk (cost=0.00..2.32 rows=53 width=0) (actual time=0.017..0.017 rows=0 loops=1)

  • Index Cond: (recip.recipient_id = tm_id)
16. 0.005 0.005 ↓ 0.0 0 1

Index Scan using recipients_recip_id_org_id_not_deleted_pidx on recipients r_memofteam (cost=0.43..1.91 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (tm.recipient_id = recipient_id)
17.          

CTE all_persons

18. 0.000 0.167 ↑ 43,043.0 1 1

Append (cost=0.25..1,681.33 rows=43,043 width=80) (actual time=0.167..0.167 rows=1 loops=1)

19. 0.000 0.165 ↓ 0.0 0 1

Nested Loop (cost=0.25..1,055.57 rows=43,000 width=80) (actual time=0.165..0.165 rows=0 loops=1)

20. 0.165 0.165 ↓ 0.0 0 1

CTE Scan on recip recip_1 (cost=0.00..195.32 rows=43 width=80) (actual time=0.165..0.165 rows=0 loops=1)

  • Filter: (recipient_cat = 'DYNAMIC_TEAM'::text)
  • Rows Removed by Filter: 1
21. 0.000 0.000 ↓ 0.0 0

Function Scan on retrieve_sortable_people_from_dynamic_team dyt (cost=0.25..10.25 rows=1,000 width=72) (never executed)

22. 0.002 0.002 ↑ 43.0 1 1

CTE Scan on recip recip_2 (cost=0.00..195.32 rows=43 width=80) (actual time=0.001..0.002 rows=1 loops=1)

  • Filter: (recipient_cat = 'PERSON'::text)
23.          

CTE replacements

24. 0.002 1.982 ↓ 0.0 0 1

Nested Loop (cost=0.25..861,721.11 rows=43,043,000 width=112) (actual time=1.982..1.982 rows=0 loops=1)

25. 0.169 0.169 ↑ 43,043.0 1 1

CTE Scan on all_persons (cost=0.00..860.86 rows=43,043 width=72) (actual time=0.168..0.169 rows=1 loops=1)

26. 1.811 1.811 ↓ 0.0 0 1

Function Scan on retrieve_replacements rr (cost=0.25..10.25 rows=1,000 width=112) (actual time=1.811..1.811 rows=0 loops=1)

Planning time : 6.207 ms
Execution time : 3.830 ms