explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fXWa

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 16,081.178 ↓ 2.0 20 1

Subquery Scan on relation_list (cost=1,415,662.75..1,415,662.88 rows=10 width=4) (actual time=16,081.148..16,081.178 rows=20 loops=1)

2. 0.023 16,081.172 ↓ 2.0 20 1

Limit (cost=1,415,662.75..1,415,662.78 rows=10 width=1,299) (actual time=16,081.147..16,081.172 rows=20 loops=1)

3. 146.497 16,081.149 ↓ 2.0 20 1

Sort (cost=1,415,662.75..1,415,662.78 rows=10 width=1,299) (actual time=16,081.146..16,081.149 rows=20 loops=1)

  • Sort Key: relation_grouped.id DESC
  • Sort Method: top-N heapsort Memory: 26kB
4. 158.238 15,934.652 ↓ 34,115.7 341,157 1

Subquery Scan on relation_grouped (cost=1,415,657.39..1,415,662.59 rows=10 width=1,299) (actual time=12,357.100..15,934.652 rows=341,157 loops=1)

5. 0.000 15,776.414 ↓ 34,115.7 341,157 1

Group (cost=1,415,657.39..1,415,662.49 rows=10 width=769) (actual time=12,357.096..15,776.414 rows=341,157 loops=1)

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

Initplan (for Group)

7. 266.291 10,037.365 ↓ 10.4 341,157 1

HashAggregate (cost=1,415,186.22..1,415,515.37 rows=32,915 width=4) (actual time=9,927.089..10,037.365 rows=341,157 loops=1)

  • Group Key: edl.entity_id
8.          

CTE user_data_filter

9. 45.520 52.485 ↓ 1.0 155,719 1

Bitmap Heap Scan on user_data_access (cost=3,406.92..42,415.01 rows=148,967 width=48) (actual time=7.132..52.485 rows=155,719 loops=1)

  • Recheck Cond: (user_id = 1199)
  • Filter: (NOT deleted)
  • Heap Blocks: exact=1509
10. 6.965 6.965 ↓ 1.0 155,719 1

Bitmap Index Scan on idx_user_data_access_user_id (cost=0.00..3,369.68 rows=148,967 width=0) (actual time=6.964..6.965 rows=155,719 loops=1)

  • Index Cond: (user_id = 1199)
11. 82.971 9,771.074 ↓ 10.5 346,244 1

Append (cost=343,694.13..1,372,688.92 rows=32,915 width=4) (actual time=1,050.660..9,771.074 rows=346,244 loops=1)

12. 308.601 1,649.814 ↓ 11.7 346,244 1

Merge Join (cost=343,694.13..352,148.10 rows=29,709 width=4) (actual time=1,050.659..1,649.814 rows=346,244 loops=1)

  • Merge Cond: ((edl.ancestor_id = da.entity_id) AND (edl.ancestor_type_id = da.entity_type_id))
13. 612.773 1,020.847 ↓ 1.0 1,023,780 1

Sort (cost=334,687.31..337,220.06 rows=1,013,099 width=12) (actual time=799.776..1,020.847 rows=1,023,780 loops=1)

  • Sort Key: edl.ancestor_id, edl.ancestor_type_id
  • Sort Method: external merge Disk: 22056kB
14. 408.074 408.074 ↓ 1.0 1,023,780 1

Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edl (cost=0.56..216,314.44 rows=1,013,099 width=12) (actual time=0.021..408.074 rows=1,023,780 loops=1)

  • Index Cond: (entity_type_id = 1)
  • Filter: (NOT deleted)
15. 184.481 320.366 ↓ 5.0 375,931 1

Sort (cost=9,006.82..9,193.03 rows=74,484 width=8) (actual time=250.813..320.366 rows=375,931 loops=1)

  • Sort Key: da.entity_id, da.entity_type_id
  • Sort Method: external sort Disk: 3360kB
16. 135.885 135.885 ↓ 2.1 155,719 1

CTE Scan on user_data_filter da (cost=0.00..2,979.34 rows=74,484 width=8) (actual time=7.137..135.885 rows=155,719 loops=1)

  • Filter: self_access
17. 0.001 8,038.289 ↓ 0.0 0 1

Nested Loop (cost=871,018.75..1,020,047.10 rows=3,206 width=4) (actual time=8,038.289..8,038.289 rows=0 loops=1)

  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
18. 0.004 8,038.288 ↓ 0.0 0 1

Merge Join (cost=871,018.19..897,472.54 rows=96,810 width=40) (actual time=8,038.288..8,038.288 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))
19. 1,417.599 8,020.977 ↑ 3,301,357.0 1 1

Sort (cost=862,011.36..870,264.76 rows=3,301,357 width=16) (actual time=8,020.977..8,020.977 rows=1 loops=1)

  • Sort Key: edl_1.ancestor_id, edl_1.ancestor_type_id
  • Sort Method: external merge Disk: 84368kB
20. 6,603.378 6,603.378 ↓ 1.0 3,312,396 1

Seq Scan on entity_data_link edl_1 (cost=0.00..448,143.08 rows=3,301,357 width=16) (actual time=0.018..6,603.378 rows=3,312,396 loops=1)

  • Filter: (parent AND (NOT deleted))
  • Rows Removed by Filter: 14519610
21. 0.007 17.307 ↓ 0.0 0 1

Sort (cost=9,006.82..9,193.03 rows=74,484 width=40) (actual time=17.307..17.307 rows=0 loops=1)

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

CTE Scan on user_data_filter da_1 (cost=0.00..2,979.34 rows=74,484 width=40) (actual time=17.300..17.300 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 155719
23. 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.56..1.20 rows=3 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)
24. 275.513 12,445.563 ↓ 34,115.7 341,157 1

Sort (cost=142.02..142.04 rows=10 width=60) (actual time=12,357.037..12,445.563 rows=341,157 loops=1)

  • Sort Key: r.id, tz.time_zone, crp.color
  • Sort Method: external merge Disk: 24776kB
25. 407.985 12,170.050 ↓ 34,115.7 341,157 1

Nested Loop Left Join (cost=2.78..141.85 rows=10 width=60) (actual time=10,180.874..12,170.050 rows=341,157 loops=1)

26. 171.134 11,079.751 ↓ 34,115.7 341,157 1

Hash Left Join (cost=2.35..57.20 rows=10 width=60) (actual time=10,180.856..11,079.751 rows=341,157 loops=1)

  • Hash Cond: (r.time_zone_id = tz.id)
27. 10,908.589 10,908.589 ↓ 34,115.7 341,157 1

Index Scan using pk_relation on relation r (cost=0.42..55.25 rows=10 width=48) (actual time=10,180.816..10,908.589 rows=341,157 loops=1)

  • Index Cond: (id = ANY ($3))
  • Filter: ((NOT deleted) AND (client_id = 1007))
28. 0.014 0.028 ↑ 1.0 41 1

Hash (cost=1.41..1.41 rows=41 width=20) (actual time=0.027..0.028 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
29. 0.014 0.014 ↑ 1.0 41 1

Seq Scan on time_zone tz (cost=0.00..1.41 rows=41 width=20) (actual time=0.006..0.014 rows=41 loops=1)

30. 682.314 682.314 ↑ 1.0 1 341,157

Index Scan using idx_composite_rating_performance_supid_cltid_chart_id on composite_rating_performance crp (cost=0.43..8.46 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=341,157)

  • Index Cond: ((supplier_id = r.id) AND (client_id = r.client_id) AND (client_id = 1007) AND (chart_id = 2))
Planning time : 1.381 ms
Execution time : 16,105.746 ms