PERF: Add index `id DESC, baked_version` ON `posts`.

PERF: Add index id DESC, baked_version ON posts.

A scheduled job runs Post.rebake_old with a limit of 80 which does a look up for the latest posts that have not been baked to the latest version. Before this commit, the query would run using the primary key’s index and does a reverse scan. However the query performance quicky becomes bad as more and more of the latest posts have been baked to the latest version.

diff --git a/db/migrate/20190408072550_add_index_id_baked_version_on_posts.rb b/db/migrate/20190408072550_add_index_id_baked_version_on_posts.rb
new file mode 100644
index 0000000..5bcb2be
--- /dev/null
+++ b/db/migrate/20190408072550_add_index_id_baked_version_on_posts.rb
@@ -0,0 +1,7 @@
+class AddIndexIdBakedVersionOnPosts < ActiveRecord::Migration[5.2]
+  def change
+    add_index :posts, [:id, :baked_version],
+      order: { id: :desc },
+      where: "(deleted_at IS NULL)"
+  end
+end

GitHub sha: 4791d992

1 Like

It is breaking the parallel ruby test. I’m not exactly sure about the reason. topic.posts method returns posts in reverse order. Checking…

Oh … I can explain why a filtered index is what we want here.

You are selecting more or less like so:

select id from posts
where baked_version is not null and 
  baked_version <> 2 and
  deleted_at is null
order id desc

This is way of making this more efficient is by adding

create index idx on posts (
  id desc
) where deleted_at is null and baked_version is not null and baked_version <> 2

That way as you reindex the index keeps on getting smaller until it has 0 size.

To grab the next id you just have to head to the first entry in the index.

Once you change that it is no longer there.

This index does not help…

create index idx on posts (
   id desc, baked_version
) where deleted_at is null

Imagine you have the records

id   |    baked_version
1    |  2    
2    |  2    
3    |  1    
4    |  2    
5    |  2    

To find 3,1 you still got to scan the 5,2 and 4,2, all the index placed does is replaced a table scan with an index scan. We should be seeking here which is way cheaper.

3 Likes

PERF: Add `index_for_rebake_old` to `posts`.

Followed up :slight_smile: Thanks for the tip :+1:

2 Likes