How to solve High Response Time in this method from my Ruby on Rails app

How to solve High Response Time in this method from my Ruby on Rails app

I have this method in my Rails API that keep sending me High Response Time alerts. I have tried to optimize it as much as I could according to my current knowledge but its still not doing the job apparently. Any help on how to optimize these queries would be much appreciated: This is my method to fetch markers and send them over to my API First I fetch the addresses longitude params:longitude latitude params:latitude addresses Address.joinsINNER JOIN users ON users.id addresses.addressable_id .joinsINNER JOIN items ON items.user_id users.id .whereitems.name IS NOT NULL.whereitems.name .whereitems.visibility TRUE .whereitems.photo IS NOT NULL.whereitems.photo .whereaddresses.latitude IS NOT NULL AND addresses.addressable_type , User .nearlatitude, longitude, params:distance.to_i 1000 Second, I use these addresses to render a JSON object back to my API I have a checkitem method def checkitemitem begin requests Request.whereitem_id AND created_at AND created_at , item.id, DateTime.now - 1.day, DateTime.now - 6.months.pluck:status if requests.exists requests.count 2 if requests.countpending 3 item.user.current_sign_in_at.present item.user.current_sign_in_at DateTime.now - 2.weeks false else true end elsif requests requests.count 2 true elsif item.user.current_sign_in_at.present item.user.current_sign_in_at DateTime.now - 2.weeks item.user.created_at DateTime.now - 2.weeks true else false end rescue true end end Then I render my JSON places Address.whereaddressable_type: Item.where.nottype_add: nil.nearlatitude, longitude, 10 render json: markers: addresses.uniq.map address name: address.user.items.first.name, photo: uri: address.user.items.first.photo.url , id: Item.whereuser_id: address.addressable_id.first.id, latitude: address.latitude, longitude: address.longitude, breed: address.user.items.first.breed.id, innactive: checkitemaddress.user.items.first false : true, power: address.user.items.first.requests.count 2 address.user.items.first.requests.last3.map:status.countpending 1 true : false, .reject e e.nil .flatten.first100 end address.explain EXPLAIN for: SELECT addresses., 3958.755864232 2 ASINSQRTPOWERSIN45.501689 - addresses.latitude PI 180 2, 2 COS45.501689 PI 180 COSaddresses.latitude PI 180 POWERSIN-73.567256 - addresses.longitude PI 180 2, 2 AS distance, MODCASTATAN2 addresses.longitude - -73.567256 57.2957795, addresses.latitude - 45.501689 57.2957795 57.2957795 360 AS decimal, 360 AS bearing FROM addresses INNER JOIN users ON users.id addresses.addressable_id INNER JOIN items ON items.user_id users.id WHERE items.name IS NOT NULL AND items.name AND items.visibility TRUE AND items.photo IS NOT NULL AND items.photo AND addresses.latitude IS NOT NULL AND addresses.addressable_type User AND addresses.latitude BETWEEN 31.028510688915205 AND 59.97486731108479 AND addresses.longitude BETWEEN -94.21702228070411 AND -52.91748971929589 AND 3958.755864232 2 ASINSQRTPOWERSIN45.501689 - addresses.latitude PI 180 2, 2 COS45.501689 PI 180 COSaddresses.latitude PI 180 POWERSIN-73.567256 - addresses.longitude PI 180 2, 2 BETWEEN 0.0 AND 1000 ORDER BY distance ASC QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort cost224.28..224.28 rows1 width138 Sort Key: 7917.511728464::double precision asinsqrtpowersin45.501689::double precision - addresses.latitude 3.14159265358979::double precision 180::double precision 2::double precision, 2::double precision 0.70088823836273::double precision cosaddresses.latitude 3.14159265358979::double precision 180::double precision powersin-73.567256::double precision - addresses.longitude 3.14159265358979::double precision 180::double precision 2::double precision, 2::double precision - Nested Loop cost0.11..224.28 rows1 width138 - Nested Loop cost0.06..207.10 rows39 width8 - Seq Scan on items cost0.00..126.62 rows39 width4 Filter: name IS NOT NULL AND visibility AND photo IS NOT NULL AND name::text ::text AND photo::text ::text - Index Only Scan using users_pkey on users cost0.06..2.06 rows1 width4 Index Cond: id items.user_id - Index Scan using index_addresses_on_addressable_type_and_addressable_id on addresses cost0.06..0.44 rows1 width98 Index Cond: addressable_type::text User::text AND addressable_id users.id Filter: latitude IS NOT NULL AND latitude 31.0285106889152::double precision AND latitude 59.9748673110848::double precision AND longitude -94.2170222807041::double precision AND longitude -52.9174897192959::double precision AND 7917.511728464::double precision asinsqrtpowersin45.501689::double precision - latitude 3.14159265358979::double precision 180::double precision 2::double precision, 2::double precision 0.70088823836273::double precision coslatitude 3.14159265358979::double precision 180::double precision powersin-73.567256::double precision - longitude 3.14159265358979::double precision 180::double precision 2::double precision, 2::double precision 0::double precision AND 7917.511728464::double precision asinsqrtpowersin45.501689::double precision - latitude 3.14159265358979::double precision 180::double precision 2::double precision, 2::double precision 0.70088823836273::double precision coslatitude 3.14159265358979::double precision 180::double precision powersin-73.567256::double precision - longitude 3.14159265358979::double precision 180::double precision 2::double precision, 2::double precision 1000::double precision 11 rows

I dont know anything about Crystal on Train Track but if your issue is specifically caused by SQL queries which are taking too long to post an output. You can try these. You join a Users_Table with addresses then You take items_Table and join it to previous operation. Before you filter these; items_name should be NOT NULL items_name should be not items_photo should be NOT NULL items_photo should be not items.visibility TRUE addresses.latitude should not be NULL and some more which can not be easily avoided i assume. I am not sure about your design but some of the above can be avoided. What i would do creating a VIEW. Static conditions between users like NOT NULLs would already be filtered and wouldnt need to be executed each time. A view called Showable_Items which are all items but items_name should be NOT NULL items_name should be not items_photo should be NOT NULL items_photo should be not items.visibility should be TRUE A view called Addressable_Addresses which are all addresses but addresses.latitude should not be NULL And join these 2 view with ready to use content. Also try; What is your most critical filter parameter. coordinates comparison possibly filters 99.9 of the table. So this table should also be divided. VIEWS again . ALL_ADDRESSES_VIEW BUT which latitude between 10 and 15 and such. whatever makes sense with your design.

You have not so easy question and my answer is built on my assumption and code that I see. I am sure that with your feedbacks and cooperation we will make it : STEP1: You can improve your fetch addresses code as follows: addresses Address.joinsINNER JOIN users ON users.id addresses.addressable_id AND addresses.addressable_type User INNER JOIN items ON items.user_id users.id .where.not items: name: nil, , photo: nil, , visibility: false , addresses: latitude: nil .nearlatitude, longitude, params:distance.to_i 1000 .selectaddresses., items.id AS item_id STEP2: Remove places query. At least I dont see any place where you use it STEP3: Prevent N 1 queries with includes: requests Request.whereitem_id: addresses.map:item_id.uniq.wherecreated_at AND created_at , DateTime.now - 1.day, DateTime.now - 6.months.to_a render json: markers: addresses.uniq.map address name: address.user.items.first.name, photo: uri: address.user.items.first.photo.url , id: Item.whereuser_id: address.addressable_id.first.id, latitude: address.latitude, longitude: address.longitude, breed: address.user.items.first.breed.id, innactive: checkitemaddress.user.items.first, requests false : true, power: address.user.items.first.requests.count 2 address.user.items.first.requests.last3.map:status.countpending 1 true : false, .reject e e.nil .flatten.first100 end STEP4: Remove queries from checkitem: def checkitemitem, requests begin statuses requests.select r r.item_id item.id .map:status if requests.exists requests.count 2 if requests.countpending 3 item.user.current_sign_in_at.present item.user.current_sign_in_at DateTime.now - 2.weeks false else true end elsif requests requests.count 2 true elsif item.user.current_sign_in_at.present item.user.current_sign_in_at DateTime.now - 2.weeks item.user.created_at DateTime.now - 2.weeks true else false end rescue true end end