explain.depesz.com

A tool for finding a real cause for slow queries.

Result: GFc : Without extra indexes

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.010 8954.710 ↑ 1.0 10 1

Limit (cost=109315.95..109316.12 rows=10 width=993) (actual time=8954.547..8954.710 rows=10 loops=1)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka
2. 2.618 8954.700 ↑ 3.7 140 1

Unique (cost=109313.81..109322.37 rows=519 width=993) (actual time=8952.037..8954.700 rows=140 loops=1)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka
3. 72.710 8952.082 ↑ 2.0 266 1

Sort (cost=109313.81..109314.07 rows=519 width=993) (actual time=8952.036..8952.082 rows=266 loops=1)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka
  • Sort Key: r.name, r.id, r.provider_id, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka
  • Sort Method: quicksort Memory: 1604kB
4. 0.825 8879.372 ↓ 1.7 895 1

Hash Left Join (cost=13020.62..109309.13 rows=519 width=993) (actual time=43.135..8879.372 rows=895 loops=1)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka
  • Hash Cond: (r.zipcode = zgc.zipcode)
5. 2.578 8875.776 ↓ 1.7 895 1

Hash Join (cost=12966.27..109252.92 rows=519 width=993) (actual time=40.327..8875.776 rows=895 loops=1)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka
  • Hash Cond: (gcity.resource_id = r.id)
6. 8835.441 8835.441 ↑ 19.2 2570 1

Seq Scan on xs_resource_geoserved_cities gcity (cost=0.00..96248.54 rows=49433 width=4) (actual time=1.791..8835.441 rows=2570 loops=1)

  • Output: gcity.id, gcity.resource_id, gcity.county_id, gcity.name, gcity.show_descendents, gcity.is_available
  • Filter: (lower((name)::text) = 'orlando'::text)
7. 1.865 37.757 ↓ 1.0 476 1

Hash (cost=12965.13..12965.13 rows=459 width=1001) (actual time=37.757..37.757 rows=476 loops=1)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka, gcounty.resource_id, gz.resource_id
8. 1.756 35.892 ↓ 1.0 476 1

Hash Join (cost=5198.06..12965.13 rows=459 width=1001) (actual time=23.095..35.892 rows=476 loops=1)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka, gcounty.resource_id, gz.resource_id
  • Hash Cond: (gcounty.resource_id = r.id)
9. 11.615 12.744 ↑ 1.0 5008 1

Bitmap Heap Scan on xs_resource_geoserved_counties gcounty (cost=72.06..7834.36 rows=5118 width=4) (actual time=1.641..12.744 rows=5008 loops=1)

  • Output: gcounty.id, gcounty.resource_id, gcounty.state_id, gcounty.name, gcounty.show_descendents, gcounty.is_available
  • Recheck Cond: (lower((name)::text) = 'orange'::text)
10. 1.129 1.129 ↑ 1.0 5008 1

Bitmap Index Scan on idx_xs_resource_geoserved_counties_name_lower (cost=0.00..71.80 rows=5118 width=0) (actual time=1.129..1.129 rows=5008 loops=1)

  • Index Cond: (lower((name)::text) = 'orange'::text)
11. 2.165 21.392 ↓ 1.0 475 1

Hash (cost=5124.86..5124.86 rows=459 width=997) (actual time=21.392..21.392 rows=475 loops=1)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka, gz.resource_id
12. 0.934 19.227 ↓ 1.0 475 1

Nested Loop (cost=10.85..5124.86 rows=459 width=997) (actual time=0.790..19.227 rows=475 loops=1)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka, gz.resource_id
13. 7.316 7.757 ↓ 3.8 1756 1

Bitmap Heap Scan on xs_resource_geoserved_zipcodes gz (cost=10.85..1769.56 rows=459 width=4) (actual time=0.757..7.757 rows=1756 loops=1)

  • Output: gz.id, gz.resource_id, gz.city_id, gz.zipcode, gz.is_available
  • Recheck Cond: (zipcode = 32810)
14. 0.441 0.441 ↓ 3.8 1756 1

Bitmap Index Scan on idx_xs_resource_geoserved_zipcodes_zipcode (cost=0.00..10.82 rows=459 width=0) (actual time=0.441..0.441 rows=1756 loops=1)

  • Index Cond: (zipcode = 32810)
15. 10.536 10.536 ↓ 0.0 0 1756

Index Scan using xs_resources_pkey on xs_resources r (cost=0.00..7.31 rows=1 width=993) (actual time=0.006..0.006 rows=0 loops=1756)

  • Output: r.id, r.sp_account_id, r.provider_id, r.name, r.address1, r.address2, r.city, r.state, r.zipcode, r.latitude, r.longitude, r.url, r.description, r.date_added, r.date_modified, r.is_indexed, r.parent_provider_id, r.is_shelter, r.provider_level, r.handicap_access, r.is_callcenter, r.is_available, r.phone, r.is_geocoded, r.county, r.tsv_default, r.tsv_default_no_desc, r.tsv_wishlist, r.tsv_volunteer, r.volunteer, r.wishlist, r.aka
  • Index Cond: (r.id = gz.resource_id)
  • Filter: (r.is_available AND (r.sp_account_id = 48))
16. 1.696 2.771 ↓ 1.0 5412 1

Hash (cost=40.82..40.82 rows=5410 width=4) (actual time=2.771..2.771 rows=5412 loops=1)

  • Output: zgc.zipcode
17. 1.075 1.075 ↓ 1.0 5412 1

Seq Scan on xs_geocache zgc (cost=0.00..40.82 rows=5410 width=4) (actual time=0.007..1.075 rows=5412 loops=1)

  • Output: zgc.zipcode