explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EuVAl

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

Subquery Scan on relation_list (cost=500.60..500.72 rows=10 width=4) (actual time=1,456.714..1,456.726 rows=20 loops=1)

2. 0.010 1,456.723 ↓ 2.0 20 1

Limit (cost=500.60..500.62 rows=10 width=1,299) (actual time=1,456.713..1,456.723 rows=20 loops=1)

3. 18.611 1,456.713 ↓ 2.0 20 1

Sort (cost=500.60..500.62 rows=10 width=1,299) (actual time=1,456.712..1,456.713 rows=20 loops=1)

  • Sort Key: relation_grouped.id DESC
  • Sort Method: top-N heapsort Memory: 26kB
4. 13.419 1,438.102 ↓ 6,427.1 64,271 1

Subquery Scan on relation_grouped (cost=495.23..500.43 rows=10 width=1,299) (actual time=598.552..1,438.102 rows=64,271 loops=1)

5. 763.552 1,424.683 ↓ 6,427.1 64,271 1

Group (cost=495.23..500.33 rows=10 width=786) (actual time=598.549..1,424.683 rows=64,271 loops=1)

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

Initplan (for Group)

7. 25.451 58.120 ↓ 10,711.8 64,271 1

HashAggregate (cost=384.09..384.15 rows=6 width=4) (actual time=47.032..58.120 rows=64,271 loops=1)

  • Group Key: edl.entity_id
8.          

CTE user_data_filter

9. 0.240 0.240 ↓ 6.1 525 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.42..100.96 rows=86 width=48) (actual time=0.017..0.240 rows=525 loops=1)

  • Index Cond: (user_id = 2156009)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 5
10. 5.796 32.669 ↓ 10,711.8 64,271 1

Append (cost=0.42..283.11 rows=6 width=4) (actual time=0.447..32.669 rows=64,271 loops=1)

11. 5.996 26.831 ↓ 12,854.2 64,271 1

Nested Loop (cost=0.42..115.99 rows=5 width=4) (actual time=0.447..26.831 rows=64,271 loops=1)

12. 0.360 0.360 ↓ 12.2 525 1

CTE Scan on user_data_filter da (cost=0.00..1.72 rows=43 width=8) (actual time=0.018..0.360 rows=525 loops=1)

  • Filter: self_access
13. 20.475 20.475 ↓ 122.0 122 525

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edl (cost=0.42..2.65 rows=1 width=12) (actual time=0.001..0.039 rows=122 loops=525)

  • Index Cond: ((entity_type_id = 1) AND (ancestor_type_id = da.entity_type_id) AND (ancestor_id = da.entity_id))
  • Filter: (NOT deleted)
14. 0.001 0.042 ↓ 0.0 0 1

Nested Loop (cost=0.85..167.03 rows=1 width=4) (actual time=0.042..0.042 rows=0 loops=1)

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

Nested Loop (cost=0.42..163.96 rows=6 width=40) (actual time=0.041..0.041 rows=0 loops=1)

16. 0.041 0.041 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..1.72 rows=43 width=40) (actual time=0.041..0.041 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 525
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.42..3.76 rows=1 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.42..0.49 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. 21.975 603.011 ↓ 6,427.1 64,271 1

Sort (cost=111.08..111.11 rows=10 width=77) (actual time=598.478..603.011 rows=64,271 loops=1)

  • Sort Key: r.id, tz.time_zone, crp.color
  • Sort Method: quicksort Memory: 11033kB
20. 0.000 581.036 ↓ 6,427.1 64,271 1

Nested Loop Left Join (cost=0.98..110.91 rows=10 width=77) (actual time=76.993..581.036 rows=64,271 loops=1)

21. 6.003 521.629 ↓ 6,427.1 64,271 1

Nested Loop Left Join (cost=0.84..106.02 rows=10 width=65) (actual time=76.987..521.629 rows=64,271 loops=1)

22. 194.271 194.271 ↓ 6,427.1 64,271 1

Index Scan using idx_relation_2_deleted_client_id_id on relation r (cost=0.41..23.19 rows=10 width=65) (actual time=76.969..194.271 rows=64,271 loops=1)

  • Index Cond: ((deleted = false) AND (client_id = 1023) AND (id = ANY ($7)))
  • Filter: (NOT deleted)
23. 321.355 321.355 ↑ 1.0 1 64,271

Index Scan using idx_composite_rating_performance_supid_cltid on composite_rating_performance crp (cost=0.42..8.27 rows=1 width=12) (actual time=0.002..0.005 rows=1 loops=64,271)

  • Index Cond: ((supplier_id = r.id) AND (client_id = r.client_id) AND (client_id = 1023))
  • Filter: (chart_id = 2)
  • Rows Removed by Filter: 6
24. 64.271 64.271 ↑ 1.0 1 64,271

Index Scan using pk_time_zone on time_zone tz (cost=0.14..0.49 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=64,271)

  • Index Cond: (id = r.time_zone_id)
Planning time : 1.080 ms
Execution time : 1,457.330 ms