explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 54DP

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

Subquery Scan on relation_list (cost=172,156.02..172,156.27 rows=20 width=4) (actual time=2,693.894..2,693.911 rows=20 loops=1)

2. 0.015 2,693.908 ↑ 1.0 20 1

Limit (cost=172,156.02..172,156.07 rows=20 width=1,315) (actual time=2,693.893..2,693.908 rows=20 loops=1)

3. 19.112 2,693.893 ↑ 1.7 20 1

Sort (cost=172,156.02..172,156.10 rows=34 width=1,315) (actual time=2,693.892..2,693.893 rows=20 loops=1)

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

Subquery Scan on relation_grouped (cost=172,120.30..172,155.15 rows=34 width=1,315) (actual time=1,079.137..2,674.781 rows=64,271 loops=1)

5. 1,510.764 2,658.510 ↓ 1,890.3 64,271 1

Group (cost=172,120.30..172,154.81 rows=34 width=818) (actual time=1,079.135..2,658.510 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. 28.000 64.791 ↓ 10,711.8 64,271 1

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

  • Group Key: edl.entity_id
8.          

CTE user_data_filter

9. 0.520 0.520 ↓ 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.047..0.520 rows=525 loops=1)

  • Index Cond: (user_id = 2,156,009)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 5
10. 4.628 36.791 ↓ 10,711.8 64,271 1

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

11. 5.754 32.122 ↓ 12,854.2 64,271 1

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

12. 0.643 0.643 ↓ 12.2 525 1

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

  • Filter: self_access
13. 25.725 25.725 ↓ 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.049 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.041 ↓ 0.0 0 1

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

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

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

16. 0.040 0.040 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..1.72 rows=43 width=40) (actual time=0.040..0.040 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. 24.288 1,082.955 ↓ 1,890.3 64,271 1

Sort (cost=171,736.15..171,736.24 rows=34 width=93) (actual time=1,078.630..1,082.955 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: 11,842kB
20. 20.840 1,058.667 ↓ 1,890.3 64,271 1

Merge Left Join (cost=82,254.04..171,735.29 rows=34 width=93) (actual time=401.649..1,058.667 rows=64,271 loops=1)

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

Nested Loop Left Join (cost=82,253.61..82,514.24 rows=10 width=85) (actual time=399.521..806.007 rows=64,271 loops=1)

22. 29.485 463.961 ↓ 6,427.1 64,271 1

Merge Left Join (cost=82,253.19..82,431.41 rows=10 width=85) (actual time=399.490..463.961 rows=64,271 loops=1)

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

Sort (cost=27.74..27.76 rows=10 width=77) (actual time=252.003..259.695 rows=64,271 loops=1)

  • Sort Key: r.id
  • Sort Method: quicksort Memory: 11,303kB
24. 11.773 237.526 ↓ 6,427.1 64,271 1

Merge Left Join (cost=23.50..27.57 rows=10 width=77) (actual time=222.697..237.526 rows=64,271 loops=1)

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

Sort (cost=23.36..23.38 rows=10 width=65) (actual time=222.686..225.746 rows=64,271 loops=1)

  • Sort Key: r.time_zone_id
  • Sort Method: quicksort Memory: 10,205kB
26. 204.615 204.615 ↓ 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=83.513..204.615 rows=64,271 loops=1)

  • Index Cond: ((deleted = false) AND (client_id = 1,023) AND (id = ANY ($7)))
  • Filter: (NOT deleted)
27. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

28. 19.296 174.781 ↓ 11.0 64,941 1

GroupAggregate (cost=82,225.45..82,329.56 rows=5,924 width=12) (actual time=147.384..174.781 rows=64,941 loops=1)

  • Group Key: au.entity_id
29. 24.522 155.485 ↓ 10.9 65,096 1

Sort (cost=82,225.45..82,240.41 rows=5,983 width=12) (actual time=147.376..155.485 rows=65,096 loops=1)

  • Sort Key: au.entity_id
  • Sort Method: quicksort Memory: 4,589kB
30. 130.963 130.963 ↓ 10.9 65,115 1

Index Scan using idx_other_audit_log_4 on other_audit_log au (cost=0.43..81,850.12 rows=5,983 width=12) (actual time=0.067..130.963 rows=65,115 loops=1)

  • Index Cond: (entity_type_id = 1)
  • Filter: ((work_flow_status_id = 5) OR (action_id = 9))
  • Rows Removed by Filter: 358,328
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 = 1,023))
  • Filter: (chart_id = 2)
  • Rows Removed by Filter: 6
32. 51.074 231.820 ↑ 3.4 65,511 1

GroupAggregate (cost=0.43..86,415.18 rows=224,440 width=12) (actual time=0.052..231.820 rows=65,511 loops=1)

  • Group Key: au_1.entity_id
33. 180.746 180.746 ↑ 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,053.41 rows=423,474 width=12) (actual time=0.029..180.746 rows=423,302 loops=1)

  • Index Cond: (entity_type_id = 1)
Planning time : 3.208 ms
Execution time : 2,696.140 ms