explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Inqs

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2,664.227 ↑ 1.0 20 1

Subquery Scan on relation_list (cost=172,596.77..172,597.02 rows=20 width=4) (actual time=2,664.213..2,664.227 rows=20 loops=1)

2. 0.013 2,664.224 ↑ 1.0 20 1

Limit (cost=172,596.77..172,596.82 rows=20 width=1,315) (actual time=2,664.212..2,664.224 rows=20 loops=1)

3. 18.572 2,664.211 ↑ 1.7 20 1

Sort (cost=172,596.77..172,596.86 rows=34 width=1,315) (actual time=2,664.210..2,664.211 rows=20 loops=1)

  • Sort Key: relation_grouped.id DESC
  • Sort Method: top-N heapsort Memory: 26kB
4. 16.875 2,645.639 ↓ 1,890.3 64,271 1

Subquery Scan on relation_grouped (cost=172,561.06..172,595.91 rows=34 width=1,315) (actual time=1,048.134..2,645.639 rows=64,271 loops=1)

5. 1,515.301 2,628.764 ↓ 1,890.3 64,271 1

Group (cost=172,561.06..172,595.57 rows=34 width=818) (actual time=1,048.132..2,628.764 rows=64,271 loops=1)

  • Group Key: r.id, tz.time_zone, crp.color, (max(au_1.date_created)), (max(au.date_created))
6.          

Initplan (for Group)

7. 29.551 60.851 ↓ 10,711.8 64,271 1

HashAggregate (cost=799.52..799.58 rows=6 width=4) (actual time=45.336..60.851 rows=64,271 loops=1)

  • Group Key: edl.entity_id
8.          

CTE user_data_filter

9. 0.252 0.252 ↓ 2.9 525 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.42..209.38 rows=182 width=48) (actual time=0.019..0.252 rows=525 loops=1)

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

Append (cost=0.42..590.13 rows=6 width=4) (actual time=0.454..31.300 rows=64,271 loops=1)

11. 5.379 26.222 ↓ 12,854.2 64,271 1

Nested Loop (cost=0.42..244.37 rows=5 width=4) (actual time=0.453..26.222 rows=64,271 loops=1)

12. 0.368 0.368 ↓ 5.8 525 1

CTE Scan on user_data_filter da (cost=0.00..3.64 rows=91 width=8) (actual time=0.020..0.368 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.64 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.043 ↓ 0.0 0 1

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

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

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

16. 0.042 0.042 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..3.64 rows=91 width=40) (actual time=0.042..0.042 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.71 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. 25.582 1,052.612 ↓ 1,890.3 64,271 1

Sort (cost=171,761.48..171,761.56 rows=34 width=93) (actual time=1,048.055..1,052.612 rows=64,271 loops=1)

  • Sort Key: r.id, tz.time_zone, crp.color, (max(au_1.date_created)), (max(au.date_created))
  • Sort Method: quicksort Memory: 11842kB
20. 20.647 1,027.030 ↓ 1,890.3 64,271 1

Merge Left Join (cost=82,265.75..171,760.61 rows=34 width=93) (actual time=364.257..1,027.030 rows=64,271 loops=1)

  • Merge Cond: (r.id = au_1.entity_id)
21. 26.007 774.306 ↓ 6,427.1 64,271 1

Nested Loop Left Join (cost=82,265.31..82,526.15 rows=10 width=85) (actual time=362.168..774.306 rows=64,271 loops=1)

22. 29.026 426.944 ↓ 6,427.1 64,271 1

Merge Left Join (cost=82,264.89..82,443.32 rows=10 width=85) (actual time=362.143..426.944 rows=64,271 loops=1)

  • Merge Cond: (r.id = au.entity_id)
23. 23.038 241.435 ↓ 6,427.1 64,271 1

Sort (cost=27.95..27.97 rows=10 width=77) (actual time=233.426..241.435 rows=64,271 loops=1)

  • Sort Key: r.id
  • Sort Method: quicksort Memory: 11303kB
24. 11.633 218.397 ↓ 6,427.1 64,271 1

Merge Left Join (cost=23.71..27.78 rows=10 width=77) (actual time=203.656..218.397 rows=64,271 loops=1)

  • Merge Cond: (r.time_zone_id = tz.id)
25. 20.174 206.755 ↓ 6,427.1 64,271 1

Sort (cost=23.57..23.60 rows=10 width=65) (actual time=203.641..206.755 rows=64,271 loops=1)

  • Sort Key: r.time_zone_id
  • Sort Method: quicksort Memory: 10205kB
26. 186.581 186.581 ↓ 6,427.1 64,271 1

Index Scan using idx_client_id_del_name on relation r (cost=0.41..23.41 rows=10 width=65) (actual time=79.583..186.581 rows=64,271 loops=1)

  • Index Cond: ((client_id = 1023) AND (id = ANY ($7)) AND (deleted = false))
  • Filter: (NOT deleted)
27. 0.009 0.009 ↑ 41.0 1 1

Index Scan using pk_time_zone on time_zone tz (cost=0.14..4.06 rows=41 width=20) (actual time=0.009..0.009 rows=1 loops=1)

28. 19.507 156.483 ↓ 10.9 64,941 1

GroupAggregate (cost=82,236.94..82,341.18 rows=5,931 width=12) (actual time=128.611..156.483 rows=64,941 loops=1)

  • Group Key: au.entity_id
29. 24.448 136.976 ↓ 10.9 65,096 1

Sort (cost=82,236.94..82,251.92 rows=5,990 width=12) (actual time=128.601..136.976 rows=65,096 loops=1)

  • Sort Key: au.entity_id
  • Sort Method: quicksort Memory: 4589kB
30. 112.528 112.528 ↓ 10.9 65,114 1

Index Scan using idx_other_audit_log_4 on other_audit_log au (cost=0.43..81,861.12 rows=5,990 width=12) (actual time=0.036..112.528 rows=65,114 loops=1)

  • Index Cond: (entity_type_id = 1)
  • Filter: ((work_flow_status_id = 5) OR (action_id = 9))
  • Rows Removed by Filter: 358322
31. 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
32. 49.478 232.077 ↑ 3.4 65,511 1

GroupAggregate (cost=0.43..86,427.26 rows=224,547 width=12) (actual time=0.041..232.077 rows=65,511 loops=1)

  • Group Key: au_1.entity_id
33. 182.599 182.599 ↑ 1.0 423,302 1

Index Scan using idx_other_audit_log_entity_type_id_entity_id on other_audit_log au_1 (cost=0.43..82,061.97 rows=423,963 width=12) (actual time=0.016..182.599 rows=423,302 loops=1)

  • Index Cond: (entity_type_id = 1)
Planning time : 1.385 ms
Execution time : 2,665.743 ms