Rails - Optimizing DB Queries

Question

I'm in the final stages of an MVP for a new pet project... and trying to improve the query times.

My primary table currently has about 150k records... and that's expected to grow to well over 1million... perhaps even 10million plus. These records belong to a parent that can be blocked by the user. When the parent is blocked, the child should no longer be visible in any views until the parent is unblocked. Users can have upwards of 100 or more blocked parents...

At the moment, I have a scope that receives an array of blocked parent_ids...

scope :parent_filter, ->(parent_ids) { where.not(parent_id: [*parent_ids]) }

The parent_id is indexed in the child table.

This is resulting in query times of about 250-350ms.... Is there a better way to do this? I was thinking perhaps a 3 way join with the parent and parent_child table might be better... but not sure.

If it makes a difference, I'm on the $7 Dyno & $9 Postgres right now... Any help would be appreciated!

equivalent8 · Accepted Answer · 2017-01-16 10:41:23Z

1

in most cases all you need to do is to implement database indexes

If you already done that:

this may sound off topic but in my experience 90% of slow Queries can be solved by Application level caching (especially Model caching and Russian doll caching)

In my experience (and experience of lot of other Rails developers e.g. from what peopele invited to speak at Ruby Rouges podcast) is that you can spend weeks optimizing SQL queries, where all you needed to do is just cache a part of a complex web-app.

update

also please check "Materialized Views" https://www.sitepoint.com/speed-up-with-materialized-views-on-postgresql-and-rails/ that may also boost PosgreSQL speed

edited Jan 16, 2017 at 10:41

answered Jan 14, 2017 at 14:43

equivalent8

14.3k9 gold badges87 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tkz79 Over a year ago

Hey, the parent_id is indexed.... and I am using as much view caching as I've been able to figure out - only the view / Russian doll caching so far... My app requires get parameters at the moment... which don't work well with caching... I went through a lot of what you describe earlier during this project... and it did help a lot... just have one query when loading the main object index page that seems to be very slow, especially with that scope engaged.

tkz79 Over a year ago

Without that scope: Load (163.8ms) With it: Load (224.9ms) I was hoping that being on the $9 heroku db (that doesn't appear to have RAM), was to blame.... but every time I've thought that so far it's turned out to be something I didn't do / did wrong.

tkz79 Over a year ago

I was missing an index on my default sort column.... thank you kindly!!!

equivalent8 Over a year ago

no problem @tkz79 glad to help. I've updated the answer with Materialized Views that may help too

Collectives™ on Stack Overflow

Rails - Optimizing DB Queries

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related