PERF: Add partial index on reviewables for topic view (#10492)

PERF: Add partial index on reviewables for topic view (#10492)

On the topic view route we query for reviewables of each post in the stream, using a query that filters on two unindexed columns. This results in a Parallel Seq Scan over all rows, which can take quite some time (~20ms was seen) on forums with lots of flags

After index is added PostgreSQL planner opts for a simple Index Scan and runs in sub 1ms.

Before:

                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=11401.08..11404.87 rows=20 width=28) (actual time=19.209..19.209 rows=1 loops=1)
   Group Key: r.target_id
   ->  Gather Merge  (cost=11401.08..11404.41 rows=26 width=28) (actual time=19.202..20.419 rows=1 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial GroupAggregate  (cost=10401.06..10401.38 rows=13 width=28) (actual time=16.958..16.958 rows=0 loops=3)
               Group Key: r.target_id
               ->  Sort  (cost=10401.06..10401.09 rows=13 width=16) (actual time=16.956..16.956 rows=0 loops=3)
                     Sort Key: r.target_id
                     Sort Method: quicksort  Memory: 25kB
                     Worker 0:  Sort Method: quicksort  Memory: 25kB
                     Worker 1:  Sort Method: quicksort  Memory: 25kB
                     ->  Nested Loop  (cost=0.42..10400.82 rows=13 width=16) (actual time=15.894..16.938 rows=0 loops=3)
                           ->  Parallel Seq Scan on reviewables r  (cost=0.00..10302.47 rows=8 width=12) (actual time=15.882..16.927 rows=0 loops=3)
                                 Filter: (((target_type)::text = 'Post'::text) AND (target_id = ANY ('{7565483,7565563,7565566,7565567,7565568,7565569,7565579,7565580,7565583,7565586,7565588,7565589,7565601,7565602,7565603,7565613,7565620,7565623,7565624,7565626}'::integer[])))
                                 Rows Removed by Filter: 49183
                           ->  Index Scan using index_reviewable_scores_on_reviewable_id on reviewable_scores s  (cost=0.42..12.27 rows=2 width=8) (actual time=0.029..0.030 rows=1 loops=1)
                                 Index Cond: (reviewable_id = r.id)
 Planning Time: 0.318 ms
 Execution Time: 20.470 ms

After:

                                                                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.84..342.54 rows=20 width=28) (actual time=0.038..0.038 rows=1 loops=1)
   Group Key: r.target_id
   ->  Nested Loop  (cost=0.84..341.95 rows=31 width=16) (actual time=0.020..0.033 rows=1 loops=1)
         ->  Index Scan using index_reviewables_on_target_id on reviewables r  (cost=0.42..96.07 rows=20 width=12) (actual time=0.013..0.026 rows=1 loops=1)
               Index Cond: (target_id = ANY ('{7565483,7565563,7565566,7565567,7565568,7565569,7565579,7565580,7565583,7565586,7565588,7565589,7565601,7565602,7565603,7565613,7565620,7565623,7565624,7565626}'::integer[]))
         ->  Index Scan using index_reviewable_scores_on_reviewable_id on reviewable_scores s  (cost=0.42..12.27 rows=2 width=8) (actual time=0.005..0.005 rows=1 loops=1)
               Index Cond: (reviewable_id = r.id)
 Planning Time: 0.253 ms
 Execution Time: 0.067 ms
diff --git a/db/migrate/20200820174703_add_partial_target_id_index_to_reviewables.rb b/db/migrate/20200820174703_add_partial_target_id_index_to_reviewables.rb
new file mode 100644
index 0000000..55d2bd8
--- /dev/null
+++ b/db/migrate/20200820174703_add_partial_target_id_index_to_reviewables.rb
@@ -0,0 +1,9 @@
+# frozen_string_literal: true
+
+class AddPartialTargetIdIndexToReviewables < ActiveRecord::Migration[6.0]
+  disable_ddl_transaction!
+
+  def change
+    add_index :reviewables, [:target_id], where: "target_type = 'Post'", algorithm: :concurrently, name: "index_reviewables_on_target_id_where_post_type_eq_post"
+  end
+end

GitHub sha: 58cdf876

1 Like

This commit appears in #10492 which was approved by tgxworld. It was merged by Falco.