explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yEUM

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,473.732 ↓ 2.0 20 1

Subquery Scan on relation_list (cost=49,390.61..49,390.64 rows=10 width=4) (actual time=1,473.721..1,473.732 rows=20 loops=1)

2. 0.009 1,473.729 ↓ 2.0 20 1

Limit (cost=49,390.61..49,390.63 rows=10 width=1,299) (actual time=1,473.720..1,473.729 rows=20 loops=1)

3. 19.763 1,473.720 ↓ 2.0 20 1

Sort (cost=49,390.61..49,390.63 rows=10 width=1,299) (actual time=1,473.719..1,473.720 rows=20 loops=1)

  • Sort Key: relation_grouped.id DESC
  • Sort Method: top-N heapsort Memory: 26kB
4. 14.153 1,453.957 ↓ 7,241.8 72,418 1

Subquery Scan on relation_grouped (cost=49,388.08..49,390.53 rows=10 width=1,299) (actual time=615.875..1,453.957 rows=72,418 loops=1)

5. 748.763 1,439.804 ↓ 7,241.8 72,418 1

Group (cost=49,388.08..49,390.52 rows=10 width=792) (actual time=615.873..1,439.804 rows=72,418 loops=1)

  • Group Key: r.id, tz.time_zone, crp.color
6.          

Initplan (for Group)

7. 28.274 67.478 ↓ 118.1 72,418 1

HashAggregate (cost=49,275.70..49,276.32 rows=613 width=4) (actual time=56.254..67.478 rows=72,418 loops=1)

  • Group Key: edl.entity_id
8.          

CTE user_data_filter

9. 0.016 0.016 ↑ 1,242.8 10 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.21..13,015.21 rows=12,428 width=48) (actual time=0.010..0.016 rows=10 loops=1)

  • Index Cond: (user_id = 78,648)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
10. 4.404 39.204 ↓ 118.1 72,418 1

Append (cost=0.21..36,259.76 rows=613 width=4) (actual time=0.038..39.204 rows=72,418 loops=1)

11. 5.545 34.797 ↓ 119.9 72,418 1

Nested Loop (cost=0.21..12,273.64 rows=604 width=4) (actual time=0.038..34.797 rows=72,418 loops=1)

12. 0.022 0.022 ↑ 621.4 10 1

CTE Scan on user_data_filter da (cost=0.00..24.86 rows=6,214 width=8) (actual time=0.011..0.022 rows=10 loops=1)

  • Filter: self_access
13. 29.230 29.230 ↓ 7,242.0 7,242 10

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edl (cost=0.21..1.97 rows=1 width=12) (actual time=0.003..2.923 rows=7,242 loops=10)

  • Index Cond: ((entity_type_id = 1) AND (ancestor_type_id = da.entity_type_id) AND (ancestor_id = da.entity_id))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
14. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.41..23,985.20 rows=9 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
15. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.21..23,573.97 rows=1,513 width=40) (actual time=0.003..0.003 rows=0 loops=1)

16. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..24.86 rows=6,214 width=40) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 10
17. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_ancestor_type_id_ancestor_id on entity_data_link edl_1 (cost=0.21..3.79 rows=2 width=16) (never executed)

  • Index Cond: ((ancestor_type_id = da_1.entity_type_id) AND (ancestor_id = da_1.entity_id))
  • Filter: (parent AND (NOT deleted))
18. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edc (cost=0.21..0.26 rows=1 width=12) (never executed)

  • Index Cond: ((entity_type_id = 1) AND (ancestor_type_id = edl_1.entity_type_id) AND (ancestor_id = edl_1.entity_id))
  • Filter: (NOT deleted)
19. 37.023 623.563 ↓ 7,241.8 72,418 1

Sort (cost=111.76..111.77 rows=10 width=83) (actual time=615.820..623.563 rows=72,418 loops=1)

  • Sort Key: r.id, tz.time_zone, crp.color
  • Sort Method: external merge Disk: 6,680kB
20. 0.000 586.540 ↓ 7,241.8 72,418 1

Nested Loop Left Join (cost=0.41..111.68 rows=10 width=83) (actual time=85.682..586.540 rows=72,418 loops=1)

21. 0.000 228.431 ↓ 7,241.8 72,418 1

Nested Loop Left Join (cost=0.21..21.18 rows=10 width=83) (actual time=85.667..228.431 rows=72,418 loops=1)

22. 163.109 163.109 ↓ 7,241.8 72,418 1

Index Scan using pk_relation on relation r (cost=0.14..17.16 rows=10 width=71) (actual time=85.658..163.109 rows=72,418 loops=1)

  • Index Cond: (id = ANY ($7))
  • Filter: ((NOT deleted) AND (client_id = 1,012))
23. 72.418 72.418 ↑ 1.0 1 72,418

Index Scan using pk_time_zone on time_zone tz (cost=0.07..0.40 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=72,418)

  • Index Cond: (id = r.time_zone_id)
24. 362.090 362.090 ↑ 1.0 1 72,418

Index Scan using idx_composite_rating_performance_supid_cltid on composite_rating_performance crp (cost=0.20..9.05 rows=1 width=12) (actual time=0.002..0.005 rows=1 loops=72,418)

  • Index Cond: ((supplier_id = r.id) AND (client_id = r.client_id) AND (client_id = 1,012))
  • Filter: (chart_id = 2)
  • Rows Removed by Filter: 6
Planning time : 0.940 ms
Execution time : 1,475.425 ms