![]() Let’s convert this index to exclude NULL values and double-check if our query will still use it. ![]() Let’s assume we’re working with the following model: # db/migrate/***_create_users.rb class CreateUsers # ] We’ll distinguish objects that are currently soft-deleted by using an indexed datetime column discarded_at. Let me guide you through the process of debugging it on the example of a soft-delete feature. It might not be obvious why your queries are not using an index. They also bloat the disk usage and increase the time for backup/restore operations. If your production app has unused indexes, there’s an unnecessary overhead of updating them with each write operation. Unused_indexes is one of the more useful helper methods from rails-pg-extras gem (check out this article for info about of PG Extras implementations for other languages). ![]() Initial setup for analyzing PostgreSQL index usage However, SQL query analysis tips apply to all the web technologies like NodeJS, Python Django, or Elixir Phoenix. In the first part, we’ll discuss how the PostgreSQL query planner decides whether to use an index.Įxamples in this tutorial are based on Ruby on Rails. Read on if you’ve ever wanted to start using EXPLAIN ANALYZE for debugging slow PostgreSQL queries but did not manage to wrap your head around it. I’m starting a series of blog posts where I’ll be well… explaining it based on common use cases from web applications. Interpreting the output of SQL EXPLAIN ANALYZE might seem like dark magic at first. Or you can hire me to conduct a performance tuning and security audit of your Rails app. Check out my step-by-step guide on tuning the performance of a Rails application. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |