explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G92Z

Settings
# exclusive inclusive rows x rows loops node
1. 42.954 1,690.334 ↓ 5.6 80,079 1

HashAggregate (cost=354,890.78..355,034.84 rows=14,406 width=4) (actual time=1,673.231..1,690.334 rows=80,079 loops=1)

  • Group Key: edl.entity_id
2.          

CTE user_data_filter

3. 74.911 74.911 ↑ 1.0 175,959 1

Index Scan using idx_user_data_access_user_id_deleted on user_data_access (cost=0.43..22,786.61 rows=182,891 width=23) (actual time=0.051..74.911 rows=175,959 loops=1)

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

Append (cost=81,731.48..332,068.15 rows=14,406 width=4) (actual time=914.612..1,647.380 rows=80,079 loops=1)

5. 88.091 940.442 ↓ 5.9 80,079 1

HashAggregate (cost=81,731.48..81,868.33 rows=13,685 width=4) (actual time=914.611..940.442 rows=80,079 loops=1)

  • Group Key: edl.entity_id
6. 139.573 852.351 ↓ 11.7 160,278 1

Merge Join (cost=77,890.44..81,697.27 rows=13,685 width=4) (actual time=600.171..852.351 rows=160,278 loops=1)

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

Sort (cost=66,697.18..67,691.89 rows=397,884 width=12) (actual time=345.603..431.895 rows=401,219 loops=1)

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

Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edl (cost=0.44..29,690.01 rows=397,884 width=12) (actual time=0.038..170.243 rows=401,219 loops=1)

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

Sort (cost=11,193.26..11,421.87 rows=91,446 width=8) (actual time=254.478..280.883 rows=166,850 loops=1)

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

CTE Scan on user_data_filter da (cost=0.00..3,657.82 rows=91,446 width=8) (actual time=0.074..174.156 rows=175,959 loops=1)

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

Unique (cost=249,980.13..249,983.74 rows=721 width=4) (actual time=685.950..685.950 rows=0 loops=1)

12. 0.008 685.949 ↓ 0.0 0 1

Sort (cost=249,980.13..249,981.93 rows=721 width=4) (actual time=685.949..685.949 rows=0 loops=1)

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

Merge Join (cost=245,710.57..249,945.91 rows=721 width=4) (actual time=685.941..685.941 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. 174.956 673.735 ↑ 429,001.0 1 1

Sort (cost=234,517.31..235,589.81 rows=429,001 width=16) (actual time=673.735..673.735 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 498.779 ↑ 1.3 320,988 1

Gather (cost=1,000.88..194,382.93 rows=429,001 width=16) (actual time=0.436..498.779 rows=320,988 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
16. 143.821 507.875 ↑ 1.7 80,247 4 / 4

Nested Loop (cost=0.88..150,482.83 rows=138,387 width=16) (actual time=0.101..507.875 rows=80,247 loops=4)

17. 63.140 63.140 ↑ 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,994.67 rows=128,350 width=12) (actual time=0.034..63.140 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.007 12.201 ↓ 0.0 0 1

Sort (cost=11,193.26..11,421.87 rows=91,446 width=40) (actual time=12.200..12.201 rows=0 loops=1)

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

CTE Scan on user_data_filter da_1 (cost=0.00..3,657.82 rows=91,446 width=40) (actual time=12.194..12.194 rows=0 loops=1)

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