explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aiwr

Settings
# exclusive inclusive rows x rows loops node
1. 0.103 2,608.619 ↓ 33.4 367 1

Unique (cost=259,848.97..259,953.53 rows=11 width=32) (actual time=2,602.033..2,608.619 rows=367 loops=1)

2.          

CTE params

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

4.          

CTE portfolio_implantation_match_data

5. 0.262 7.105 ↓ 33.4 367 1

GroupAggregate (cost=285.70..285.95 rows=11 width=16) (actual time=6.786..7.105 rows=367 loops=1)

  • Group Key: portfolio_implantation_2.id
6.          

Initplan (for GroupAggregate)

7. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on params (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

8. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on params params_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

9. 0.230 6.837 ↓ 33.4 367 1

Sort (cost=285.66..285.69 rows=11 width=32) (actual time=6.773..6.837 rows=367 loops=1)

  • Sort Key: portfolio_implantation_2.id
  • Sort Method: quicksort Memory: 45kB
10. 0.061 6.607 ↓ 33.4 367 1

Nested Loop Left Join (cost=4.38..285.47 rows=11 width=32) (actual time=0.101..6.607 rows=367 loops=1)

11. 0.251 4.344 ↓ 33.4 367 1

Nested Loop Left Join (cost=3.95..192.49 rows=11 width=28) (actual time=0.096..4.344 rows=367 loops=1)

12. 0.443 1.524 ↓ 33.4 367 1

Hash Left Join (cost=3.51..99.41 rows=11 width=20) (actual time=0.088..1.524 rows=367 loops=1)

  • Hash Cond: ((portfolio_implantation_2.user_company_id = company_branch.user_company_id) AND (portfolio_implantation_2.company_branch_id = company_branch.id))
  • Filter: ((company_branch.deleted_at IS NULL) AND ((portfolio_implantation_2.company_branch_id IS NULL) OR (array_remove(ARRAY[company_branch.user_community_id], NULL::integer) && $2)))
13. 1.052 1.052 ↓ 6.3 367 1

Seq Scan on portfolio_implantation portfolio_implantation_2 (cost=0.00..95.59 rows=58 width=28) (actual time=0.031..1.052 rows=367 loops=1)

  • Filter: ((deleted_at IS NULL) AND (user_company_id = $1))
  • Rows Removed by Filter: 1603
14. 0.005 0.029 ↑ 1.2 5 1

Hash (cost=3.42..3.42 rows=6 width=20) (actual time=0.029..0.029 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.024 0.024 ↑ 1.2 5 1

Seq Scan on company_branch (cost=0.00..3.42 rows=6 width=20) (actual time=0.013..0.024 rows=5 loops=1)

  • Filter: (user_company_id = $1)
  • Rows Removed by Filter: 77
16. 2.569 2.569 ↓ 0.0 0 367

Index Scan using gen_establishment_search_establishment_id on gen_establishment_search (cost=0.43..8.45 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=367)

  • Index Cond: (establishment_id = portfolio_implantation_2.establishment_id)
17. 2.202 2.202 ↓ 0.0 0 367

Index Scan using gen_match_cap_address_pkey on gen_match_cap_address gen_match_cap_address_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=367)

  • Index Cond: (address_id = portfolio_implantation_2.main_address_id)
18. 0.267 2,608.516 ↓ 34.0 374 1

Nested Loop Left Join (cost=259,563.01..259,667.54 rows=11 width=32) (actual time=2,602.031..2,608.516 rows=374 loops=1)

  • Join Filter: (building.id = building_address.building_id)
  • Rows Removed by Join Filter: 416
19. 0.263 2,602.377 ↓ 33.4 367 1

Merge Join (cost=259,562.45..259,572.45 rows=11 width=36) (actual time=2,601.955..2,602.377 rows=367 loops=1)

  • Merge Cond: (portfolio_implantation_match_data.portfolio_implantation_id = portfolio_implantation.id)
20. 0.136 2,600.826 ↓ 33.4 367 1

Sort (cost=259,364.17..259,364.19 rows=11 width=20) (actual time=2,600.791..2,600.826 rows=367 loops=1)

  • Sort Key: portfolio_implantation_match_data.portfolio_implantation_id
  • Sort Method: quicksort Memory: 45kB
21. 0.095 2,600.690 ↓ 33.4 367 1

Hash Right Join (cost=259,212.64..259,363.98 rows=11 width=20) (actual time=2,597.658..2,600.690 rows=367 loops=1)

  • Hash Cond: ((first(portfolio_implantation_match_data_1.match_building_id)) = portfolio_implantation_match_data.match_building_id)
22. 3.184 2,575.100 ↑ 1.4 108 1

GroupAggregate (cost=259,119.28..259,268.48 rows=147 width=12) (actual time=2,572.140..2,575.100 rows=108 loops=1)

  • Group Key: gen_match_cap_address.building_id
23. 0.646 2,571.916 ↓ 5.3 781 1

Sort (cost=259,119.28..259,119.65 rows=147 width=16) (actual time=2,571.864..2,571.916 rows=781 loops=1)

  • Sort Key: gen_match_cap_address.building_id
  • Sort Method: quicksort Memory: 57kB
24. 1.521 2,571.270 ↓ 5.3 781 1

Nested Loop (cost=0.64..259,113.99 rows=147 width=16) (actual time=3.132..2,571.270 rows=781 loops=1)

25. 1,153.985 2,567.406 ↓ 5.3 781 1

Hash Right Join (cost=0.36..258,161.89 rows=147 width=20) (actual time=3.106..2,567.406 rows=781 loops=1)

  • Hash Cond: (gen_match_cap_address.building_id = portfolio_implantation_match_data_1.match_building_id)
26. 1,413.326 1,413.326 ↓ 1.0 8,937,205 1

Seq Scan on gen_match_cap_address (cost=0.00..224,663.68 rows=8,932,368 width=8) (actual time=0.012..1,413.326 rows=8,937,205 loops=1)

27. 0.046 0.095 ↓ 33.4 367 1

Hash (cost=0.22..0.22 rows=11 width=12) (actual time=0.095..0.095 rows=367 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
28. 0.049 0.049 ↓ 33.4 367 1

CTE Scan on portfolio_implantation_match_data portfolio_implantation_match_data_1 (cost=0.00..0.22 rows=11 width=12) (actual time=0.004..0.049 rows=367 loops=1)

29. 2.343 2.343 ↑ 1.0 1 781

Index Only Scan using portfolio_implantation_pkey on portfolio_implantation portfolio_implantation_1 (cost=0.28..6.48 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=781)

  • Index Cond: (id = portfolio_implantation_match_data_1.portfolio_implantation_id)
  • Heap Fetches: 369
30. 0.133 25.495 ↓ 33.4 367 1

Hash (cost=93.23..93.23 rows=11 width=20) (actual time=25.495..25.495 rows=367 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
31. 0.126 25.362 ↓ 33.4 367 1

Nested Loop Left Join (cost=0.44..93.23 rows=11 width=20) (actual time=6.834..25.362 rows=367 loops=1)

32. 7.253 7.253 ↓ 33.4 367 1

CTE Scan on portfolio_implantation_match_data (cost=0.00..0.22 rows=11 width=16) (actual time=6.789..7.253 rows=367 loops=1)

33. 17.983 17.983 ↓ 0.0 0 367

Index Only Scan using building_pkey on building (cost=0.44..8.46 rows=1 width=4) (actual time=0.049..0.049 rows=0 loops=367)

  • Index Cond: (id = portfolio_implantation_match_data.match_building_id)
  • Heap Fetches: 114
34. 0.591 1.288 ↑ 1.1 1,823 1

Sort (cost=198.28..203.20 rows=1,967 width=20) (actual time=1.154..1.288 rows=1,823 loops=1)

  • Sort Key: portfolio_implantation.id
  • Sort Method: quicksort Memory: 145kB
35. 0.697 0.697 ↓ 1.0 1,970 1

Seq Scan on portfolio_implantation (cost=0.00..90.67 rows=1,967 width=20) (actual time=0.015..0.697 rows=1,970 loops=1)

36. 5.872 5.872 ↑ 1.0 1 367

Index Scan using building_address_address_id on building_address (cost=0.56..8.58 rows=1 width=8) (actual time=0.009..0.016 rows=1 loops=367)

  • Index Cond: ((portfolio_implantation_match_data.match_main_address_id = address_id) AND (address_id = portfolio_implantation_match_data.match_main_address_id))
Planning time : 1.569 ms
Execution time : 2,608.941 ms