Attention web developers working with lots of data - below are first class problems to consider:
- slow database queries
- page time outs
When customer support barks at you to resolve a 500 error, what do you do? Initial instincts suggest the two problems are related. Especially in applications with lots of user data.
Here's step #1.. your only dance move for Ruby benchmarking: Benchmark#realtime
Here is an example from my day job at Kajabi: CX gets assigned with a customer support ticket. They log into Kajabi's super admin dashboard to search for the customer (a User). The page returns a 500 error and it doesn't render.
You want to know how long it's taking to retrieve the user. That's the first step to ensure you're going down the right rabbithole.
$ Benchmark.realtime { User.search("Sharon Jackson") }
=> 22.409309996990487
The result is the elapsed time. Searching a user cost 22 seconds! yikes.
Heroku has a threshold of 30 seconds before it fails to load a page. Postgres caches our first request so that subsequent requests read from a cached result. Heroku does this to ensure quicker response times.
I went and recorded how long it took before the page timed out. Approximately 22 seconds. Down the rabbithole you go from there.
Top comments (2)
Generic tip from my experience:
User.search()
EXPLAIN ANALYZE {SQL QUERY}
and see what's going on (you can use tools like explain.depesz.com/ to make it more readable)IIRC
.search
comes from Ransack which likely usesLIKE
orILIKE
which can be a very expensive operation (22 seconds is definitely too much) with a non trivial amount of rows to scan.LIKE
operators don't use indexes which means the DB has to sequentially scan each row to provide an answer:You could use trigrams, with the extension
pg_tgrm
to enable index scanning onLIKE
operators:Note the difference in execution time ;-)
This is a great answer:
answer re: PostgreSQL LIKE query performance variations
FTS does not support
LIKE
The previously accepted answer was incorrect. Full Text Search with its full text indexes is not for the
LIKE
operator at all, it has its own operators and doesn't work for arbitrary strings. It operates on words based on dictionaries and stemming. It does support…and here you can find more details on
pg_tgrm
: postgresql.org/docs/12/pgtrgm.htmlHope this helps :-)
I'm writing on a yellow stickie as a reminder.
I forget EXPLAIN exists time and time again. Thank you!