explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l9wx

Settings
# exclusive inclusive rows x rows loops node
1. 43.663 1,690.781 ↓ 5.6 80,079 1

HashAggregate (cost=354,616.66..354,760.57 rows=14,391 width=4) (actual time=1,674.090..1,690.781 rows=80,079 loops=1)

  • Group Key: edl.entity_id
2.          

CTE user_data_filter

3. 73.900 73.900 ↑ 1.0 175,959 1

Index Scan using idx_user_data_access_user_id_deleted on user_data_access (cost=0.43..22,784.77 rows=182,799 width=23) (actual time=0.031..73.900 rows=175,959 loops=1)

  • Index Cond: ((deleted = false) AND (user_id = 1,467))
  • Filter: (NOT deleted)
4. 21.024 1,647.118 ↓ 5.6 80,079 1

Append (cost=81,687.97..331,795.91 rows=14,391 width=4) (actual time=898.775..1,647.118 rows=80,079 loops=1)

5. 89.634 924.876 ↓ 5.9 80,079 1

HashAggregate (cost=81,687.97..81,824.68 rows=13,671 width=4) (actual time=898.773..924.876 rows=80,079 loops=1)

  • Group Key: edl.entity_id
6. 138.154 835.242 ↓ 11.7 160,278 1

Merge Join (cost=77,848.97..81,653.79 rows=13,671 width=4) (actual time=583.229..835.242 rows=160,278 loops=1)

  • Merge Cond: ((edl.ancestor_id = da.entity_id) AND (edl.ancestor_type_id = da.entity_type_id))
7. 249.864 424.770 ↓ 1.0 401,219 1

Sort (cost=66,661.67..67,655.87 rows=397,682 width=12) (actual time=336.552..424.770 rows=401,219 loops=1)

  • Sort Key: edl.ancestor_id, edl.ancestor_type_id
  • Sort Method: external merge Disk: 8,648kB
8. 174.906 174.906 ↓ 1.0 401,219 1

Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edl (cost=0.44..29,674.75 rows=397,682 width=12) (actual time=0.022..174.906 rows=401,219 loops=1)

  • Index Cond: (entity_type_id = 12)
  • Filter: (NOT deleted)
9. 102.126 272.318 ↓ 1.8 166,850 1

Sort (cost=11,187.30..11,415.80 rows=91,400 width=8) (actual time=246.588..272.318 rows=166,850 loops=1)

  • Sort Key: da.entity_id, da.entity_type_id
  • Sort Method: quicksort Memory: 14,393kB
10. 170.192 170.192 ↓ 1.9 175,959 1

CTE Scan on user_data_filter da (cost=0.00..3,655.98 rows=91,400 width=8) (actual time=0.035..170.192 rows=175,959 loops=1)

  • Filter: self_access
11. 0.001 701.218 ↓ 0.0 0 1

Unique (cost=249,751.77..249,755.37 rows=720 width=4) (actual time=701.218..701.218 rows=0 loops=1)

12. 0.008 701.217 ↓ 0.0 0 1

Sort (cost=249,751.77..249,753.57 rows=720 width=4) (actual time=701.217..701.217 rows=0 loops=1)

  • Sort Key: edc.entity_id
  • Sort Method: quicksort Memory: 25kB
13. 0.006 701.209 ↓ 0.0 0 1

Merge Join (cost=245,486.36..249,717.60 rows=720 width=4) (actual time=701.209..701.209 rows=0 loops=1)

  • Merge Cond: ((edl_1.ancestor_id = da_1.entity_id) AND (edl_1.ancestor_type_id = da_1.entity_type_id))
  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
14. 172.725 686.218 ↑ 428,566.0 1 1

Sort (cost=234,299.06..235,370.48 rows=428,566 width=16) (actual time=686.218..686.218 rows=1 loops=1)

  • Sort Key: edl_1.ancestor_id, edl_1.ancestor_type_id
  • Sort Method: external merge Disk: 8,168kB
15. 0.000 513.493 ↑ 1.3 320,988 1

Gather (cost=1,000.88..194,208.52 rows=428,566 width=16) (actual time=0.486..513.493 rows=320,988 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
16. 152.953 521.562 ↑ 1.7 80,247 4 / 4

Nested Loop (cost=0.88..150,351.92 rows=138,247 width=16) (actual time=0.100..521.562 rows=80,247 loops=4)

17. 67.695 67.695 ↑ 1.3 100,305 4 / 4

Parallel Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edc (cost=0.44..26,980.77 rows=128,285 width=12) (actual time=0.039..67.695 rows=100,305 loops=4)

  • Index Cond: (entity_type_id = 12)
  • Filter: (NOT deleted)
18. 300.914 300.914 ↑ 1.0 1 401,219 / 4

Index Scan using idx_entity_data_link_entity_type_id_entity_id on entity_data_link edl_1 (cost=0.44..0.95 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=401,219)

  • Index Cond: ((entity_type_id = edc.ancestor_type_id) AND (entity_id = edc.ancestor_id))
  • Filter: (parent AND (NOT deleted))
  • Rows Removed by Filter: 2
19. 0.010 14.985 ↓ 0.0 0 1

Sort (cost=11,187.30..11,415.80 rows=91,400 width=40) (actual time=14.985..14.985 rows=0 loops=1)

  • Sort Key: da_1.entity_id, da_1.entity_type_id
  • Sort Method: quicksort Memory: 25kB
20. 14.975 14.975 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..3,655.98 rows=91,400 width=40) (actual time=14.975..14.975 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 175,959
Planning time : 0.780 ms
Execution time : 1,707.799 ms