explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OBQ

Settings
# exclusive inclusive rows x rows loops node
1. 3,487.839 13,767.853 ↑ 1.0 1 1

Aggregate (cost=357,693.91..357,693.92 rows=1 width=8) (actual time=13,767.852..13,767.853 rows=1 loops=1)

2. 1,879.363 10,280.014 ↓ 3.5 9,828,954 1

Merge Right Join (cost=4.64..350,767.93 rows=2,770,393 width=4) (actual time=0.492..10,280.014 rows=9,828,954 loops=1)

  • Merge Cond: (locations.provider_id = providers.id)
3. 126.122 126.122 ↑ 1.0 106,748 1

Index Scan using index_locations_on_provider_id on locations (cost=0.42..7,868.97 rows=106,748 width=8) (actual time=0.044..126.122 rows=106,748 loops=1)

4. 1,301.649 8,274.529 ↓ 3.5 9,828,954 1

Materialize (cost=3.92..323,352.93 rows=2,770,393 width=4) (actual time=0.445..8,274.529 rows=9,828,954 loops=1)

5. 951.204 6,972.880 ↓ 1.5 4,059,306 1

Merge Left Join (cost=3.92..316,426.94 rows=2,770,393 width=4) (actual time=0.442..6,972.880 rows=4,059,306 loops=1)

  • Merge Cond: (providers.id = provider_specialties.provider_id)
6. 755.350 5,688.006 ↓ 1.3 3,512,737 1

Merge Left Join (cost=3.30..288,564.36 rows=2,770,393 width=4) (actual time=0.419..5,688.006 rows=3,512,737 loops=1)

  • Merge Cond: (providers.id = provider_languages.provider_id)
7. 711.165 4,796.378 ↓ 1.2 3,246,341 1

Merge Left Join (cost=2.93..270,801.86 rows=2,770,393 width=4) (actual time=0.392..4,796.378 rows=3,246,341 loops=1)

  • Merge Cond: (providers.id = psv_statuses.provider_id)
8. 150.749 1,476.824 ↓ 1.2 407,531 1

Merge Left Join (cost=2.50..74,272.85 rows=349,182 width=4) (actual time=0.357..1,476.824 rows=407,531 loops=1)

  • Merge Cond: (providers.id = clients_providers.provider_id)
9. 55.226 877.420 ↓ 1.1 274,713 1

Merge Left Join (cost=1.78..44,314.52 rows=239,399 width=4) (actual time=0.077..877.420 rows=274,713 loops=1)

  • Merge Cond: (providers.id = dea_licenses.provider_id)
10. 127.203 820.421 ↓ 1.1 274,555 1

Merge Left Join (cost=1.44..43,478.54 rows=239,399 width=4) (actual time=0.060..820.421 rows=274,555 loops=1)

  • Merge Cond: (providers.id = state_licenses.provider_id)
11. 144.808 482.207 ↓ 1.0 239,515 1

Merge Left Join (cost=0.84..27,279.36 rows=239,399 width=4) (actual time=0.035..482.207 rows=239,515 loops=1)

  • Merge Cond: (providers.id = identities.provider_id)
12. 292.788 292.788 ↑ 1.0 239,396 1

Index Only Scan using providers_pkey on providers (cost=0.42..17,416.54 rows=239,399 width=4) (actual time=0.010..292.788 rows=239,396 loops=1)

  • Heap Fetches: 239396
13. 44.611 44.611 ↑ 1.0 239,283 1

Index Only Scan using index_identities_on_provider_id_and_id on identities (cost=0.42..6,273.29 rows=239,283 width=4) (actual time=0.021..44.611 rows=239,283 loops=1)

  • Heap Fetches: 595
14. 211.011 211.011 ↓ 1.0 221,873 1

Index Scan using index_state_licenses_on_provider_id_and_license_number on state_licenses (cost=0.42..12,828.15 rows=221,802 width=8) (actual time=0.011..211.011 rows=221,873 loops=1)

15. 1.773 1.773 ↓ 1.2 4,258 1

Index Only Scan using dea_pid_lic_num on dea_licenses (cost=0.28..194.23 rows=3,461 width=4) (actual time=0.015..1.773 rows=4,258 loops=1)

  • Heap Fetches: 4258
16. 448.655 448.655 ↓ 1.2 406,790 1

Index Only Scan using index_clients_providers_on_provider_id_and_client_id_and_id on clients_providers (cost=0.42..24,995.05 rows=349,182 width=8) (actual time=0.278..448.655 rows=406,790 loops=1)

  • Heap Fetches: 406790
17. 569.755 2,608.389 ↓ 1.7 3,244,335 1

Materialize (cost=0.43..160,136.87 rows=1,899,380 width=4) (actual time=0.032..2,608.389 rows=3,244,335 loops=1)

18. 2,038.634 2,038.634 ↑ 1.0 1,899,380 1

Index Only Scan using index_psv_statuses_on_provider_id_and_state_and_last_checked on psv_statuses (cost=0.43..155,388.42 rows=1,899,380 width=4) (actual time=0.026..2,038.634 rows=1,899,380 loops=1)

  • Heap Fetches: 1899380
19. 70.286 136.278 ↓ 19.7 932,760 1

Materialize (cost=0.29..2,728.49 rows=47,392 width=8) (actual time=0.025..136.278 rows=932,760 loops=1)

20. 65.992 65.992 ↑ 1.0 47,392 1

Index Only Scan using index_provider_languages_on_provider_id_and_language_id on provider_languages (cost=0.29..2,610.01 rows=47,392 width=8) (actual time=0.022..65.992 rows=47,392 loops=1)

  • Heap Fetches: 47392
21. 249.134 333.670 ↓ 23.9 2,197,901 1

Materialize (cost=0.29..5,171.19 rows=92,150 width=8) (actual time=0.022..333.670 rows=2,197,901 loops=1)

22. 84.536 84.536 ↑ 1.0 92,150 1

Index Only Scan using index_provider_specialties_on_provider_id_and_specialty_id on provider_specialties (cost=0.29..4,940.81 rows=92,150 width=8) (actual time=0.019..84.536 rows=92,150 loops=1)