explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eX1a

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 19,090.247 ↓ 2.0 20 1

Subquery Scan on relation_list (cost=1,684,628.52..1,684,628.64 rows=10 width=4) (actual time=19,090.219..19,090.247 rows=20 loops=1)

2. 0.022 19,090.241 ↓ 2.0 20 1

Limit (cost=1,684,628.52..1,684,628.54 rows=10 width=1,315) (actual time=19,090.218..19,090.241 rows=20 loops=1)

3. 143.711 19,090.219 ↓ 2.0 20 1

Sort (cost=1,684,628.52..1,684,628.54 rows=10 width=1,315) (actual time=19,090.217..19,090.219 rows=20 loops=1)

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

Subquery Scan on relation_grouped (cost=1,684,618.10..1,684,628.35 rows=10 width=1,315) (actual time=12,816.312..18,946.508 rows=341,157 loops=1)

5. 0.000 18,783.553 ↓ 34,115.7 341,157 1

Group (cost=1,684,618.10..1,684,628.25 rows=10 width=801) (actual time=12,816.310..18,783.553 rows=341,157 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. 249.755 9,559.796 ↓ 10.8 341,157 1

HashAggregate (cost=1,398,901.43..1,399,216.45 rows=31,502 width=4) (actual time=9,451.759..9,559.796 rows=341,157 loops=1)

  • Group Key: edl.entity_id
8.          

CTE user_data_filter

9. 42.122 48.445 ↓ 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=6.499..48.445 rows=155,719 loops=1)

  • Recheck Cond: (user_id = 1,199)
  • Filter: (NOT deleted)
  • Heap Blocks: exact=1,509
10. 6.323 6.323 ↓ 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.323..6.323 rows=155,719 loops=1)

  • Index Cond: (user_id = 1,199)
11. 80.934 9,310.041 ↓ 11.0 346,244 1

Append (cost=345,129.52..1,356,407.67 rows=31,502 width=4) (actual time=958.766..9,310.041 rows=346,244 loops=1)

12. 298.066 1,540.811 ↓ 12.2 346,244 1

Merge Join (cost=345,129.52..353,362.68 rows=28,316 width=4) (actual time=958.765..1,540.811 rows=346,244 loops=1)

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

Sort (cost=336,122.69..338,586.48 rows=985,516 width=12) (actual time=742.790..959.393 rows=1,023,780 loops=1)

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

Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edl (cost=0.56..221,166.51 rows=985,516 width=12) (actual time=0.016..387.808 rows=1,023,780 loops=1)

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

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

  • Sort Key: da.entity_id, da.entity_type_id
  • Sort Method: external sort Disk: 3,360kB
16. 120.252 120.252 ↓ 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=6.502..120.252 rows=155,719 loops=1)

  • Filter: self_access
17. 0.001 7,688.296 ↓ 0.0 0 1

Nested Loop (cost=858,607.35..1,002,572.46 rows=3,186 width=4) (actual time=7,688.296..7,688.296 rows=0 loops=1)

  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
18. 0.006 7,688.295 ↓ 0.0 0 1

Merge Join (cost=858,606.79..884,298.63 rows=92,239 width=40) (actual time=7,688.295..7,688.295 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,301.873 7,671.877 ↑ 3,210,355.0 1 1

Sort (cost=849,599.96..857,625.85 rows=3,210,355 width=16) (actual time=7,671.876..7,671.877 rows=1 loops=1)

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

Seq Scan on entity_data_link edl_1 (cost=0.00..447,786.06 rows=3,210,355 width=16) (actual time=0.727..6,370.004 rows=3,312,396 loops=1)

  • Filter: (parent AND (NOT deleted))
  • Rows Removed by Filter: 14,519,610
21. 0.010 16.412 ↓ 0.0 0 1

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

  • Sort Key: da_1.entity_id, da_1.entity_type_id
  • Sort Method: quicksort Memory: 25kB
22. 16.402 16.402 ↓ 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=16.402..16.402 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 155,719
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.21 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. 282.344 12,907.338 ↓ 34,115.7 341,157 1

Sort (cost=285,401.65..285,401.67 rows=10 width=76) (actual time=12,816.222..12,907.338 rows=341,157 loops=1)

  • Sort Key: r.id, tz.time_zone, crp.color, (max(au_1.date_created)), (max(au.date_created))
  • Sort Method: external merge Disk: 30,184kB
25. 140.524 12,624.994 ↓ 34,115.7 341,157 1

Hash Left Join (cost=276,535.34..285,401.48 rows=10 width=76) (actual time=10,027.622..12,624.994 rows=341,157 loops=1)

  • Hash Cond: (r.time_zone_id = tz.id)
26. 218.500 12,484.438 ↓ 34,115.7 341,157 1

Merge Left Join (cost=276,533.42..285,399.53 rows=10 width=64) (actual time=10,027.573..12,484.438 rows=341,157 loops=1)

  • Merge Cond: (r.id = au_1.entity_id)
27. 123.559 11,778.900 ↓ 34,115.7 341,157 1

Merge Left Join (cost=120,593.95..120,741.68 rows=10 width=56) (actual time=9,782.777..11,778.900 rows=341,157 loops=1)

  • Merge Cond: (r.id = au.entity_id)
28. 466.638 11,575.996 ↓ 34,115.7 341,157 1

Nested Loop Left Join (cost=0.85..139.99 rows=10 width=48) (actual time=9,703.423..11,575.996 rows=341,157 loops=1)

29. 10,427.044 10,427.044 ↓ 34,115.7 341,157 1

Index Scan using pk_relation on relation r (cost=0.42..55.34 rows=10 width=48) (actual time=9,703.398..10,427.044 rows=341,157 loops=1)

  • Index Cond: (id = ANY ($3))
  • Filter: ((NOT deleted) AND (client_id = 1,007))
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 = 1,007) AND (chart_id = 2))
31. 0.049 79.345 ↑ 3.4 84 1

GroupAggregate (cost=120,593.10..120,598.09 rows=285 width=12) (actual time=79.288..79.345 rows=84 loops=1)

  • Group Key: au.entity_id
32. 0.063 79.296 ↑ 3.1 91 1

Sort (cost=120,593.10..120,593.81 rows=285 width=12) (actual time=79.280..79.296 rows=91 loops=1)

  • Sort Key: au.entity_id
  • Sort Method: quicksort Memory: 29kB
33. 64.592 79.233 ↑ 3.1 91 1

Bitmap Heap Scan on other_audit_log au (cost=6,269.40..120,581.48 rows=285 width=12) (actual time=15.677..79.233 rows=91 loops=1)

  • Recheck Cond: (entity_type_id = 1)
  • Filter: ((work_flow_status_id = 5) OR (action_id = 9))
  • Rows Removed by Filter: 341,405
  • Heap Blocks: exact=7,791
34. 14.641 14.641 ↓ 1.0 341,496 1

Bitmap Index Scan on idx_other_audit_log_entity_type_id_entity_id (cost=0.00..6,269.32 rows=339,319 width=0) (actual time=14.641..14.641 rows=341,496 loops=1)

  • Index Cond: (entity_type_id = 1)
35. 158.739 487.038 ↓ 1.2 341,263 1

GroupAggregate (cost=155,939.47..161,228.09 rows=274,373 width=12) (actual time=244.648..487.038 rows=341,263 loops=1)

  • Group Key: au_1.entity_id
36. 209.671 328.299 ↓ 1.0 341,496 1

Sort (cost=155,939.47..156,787.76 rows=339,319 width=12) (actual time=244.637..328.299 rows=341,496 loops=1)

  • Sort Key: au_1.entity_id
  • Sort Method: external merge Disk: 8,704kB
37. 100.545 118.628 ↓ 1.0 341,496 1

Bitmap Heap Scan on other_audit_log au_1 (cost=6,354.15..118,969.64 rows=339,319 width=12) (actual time=19.147..118.628 rows=341,496 loops=1)

  • Recheck Cond: (entity_type_id = 1)
  • Heap Blocks: exact=7,791
38. 18.083 18.083 ↓ 1.0 341,496 1

Bitmap Index Scan on idx_other_audit_log_entity_type_id_entity_id (cost=0.00..6,269.32 rows=339,319 width=0) (actual time=18.082..18.083 rows=341,496 loops=1)

  • Index Cond: (entity_type_id = 1)
39. 0.013 0.032 ↑ 1.0 41 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
40. 0.019 0.019 ↑ 1.0 41 1

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

Planning time : 2.199 ms
Execution time : 19,118.164 ms