Slow main page - caused by DB query
The view categories_categorymetadata changed in !377 (merged) (@ghassan) performs poorly in our production instance. The listwithmeta api call that is necessary when you open the main page of community solutions takes up to 3 seconds for the backend itself to load!
Reason for the slow performance: It seems that the added has_answers filter is terribly slow, even slower than the already slow join...
Postgres explain:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=355433.08..355433.40 rows=130 width=156) (actual time=3361.224..3361.261 rows=130 loops=1)
Sort Key: categories_category.displayname
Sort Method: quicksort Memory: 60kB
Buffers: shared hit=237514
-> Nested Loop Left Join (cost=0.00..355428.51 rows=130 width=156) (actual time=44.632..3360.839 rows=130 loops=1)
Join Filter: (categories_category.id = cc.id)
Rows Removed by Join Filter: 16770
Buffers: shared hit=237511
-> Seq Scan on categories_category (cost=0.00..4.30 rows=130 width=112) (actual time=0.007..0.046 rows=130 loops=1)
Buffers: shared hit=3
-> Materialize (cost=0.00..355171.04 rows=130 width=44) (actual time=0.343..25.818 rows=130 loops=130)
Buffers: shared hit=237508
-> WindowAgg (cost=0.00..355169.09 rows=130 width=44) (actual time=44.614..3353.899 rows=130 loops=1)
Buffers: shared hit=237508
-> Seq Scan on categories_category cc (cost=0.00..4.30 rows=130 width=4) (actual time=0.005..0.140 rows=130 loops=1)
Buffers: shared hit=3
SubPlan 1
-> Aggregate (cost=29.52..29.53 rows=1 width=8) (actual time=0.031..0.032 rows=1 loops=130)
Buffers: shared hit=791
-> Bitmap Heap Scan on answers_exam ae (cost=4.38..29.49 rows=13 width=0) (actual time=0.013..0.022 rows=12 loops=130)
Recheck Cond: (category_id = cc.id)
Filter: public
Rows Removed by Filter: 0
Heap Blocks: exact=525
Buffers: shared hit=791
-> Bitmap Index Scan on answers_exam_category_id_ae65ab1f (cost=0.00..4.38 rows=13 width=0) (actual time=0.008..0.008 rows=12 loops=130)
Index Cond: (category_id = cc.id)
Buffers: shared hit=266
SubPlan 2
-> Aggregate (cost=303.67..303.68 rows=1 width=8) (actual time=0.288..0.289 rows=1 loops=130)
Buffers: shared hit=31739
-> Nested Loop Semi Join (cost=9.24..303.64 rows=12 width=0) (actual time=0.101..0.285 rows=6 loops=130)
Buffers: shared hit=31739
-> Bitmap Heap Scan on answers_exam ae_1 (cost=4.38..29.49 rows=13 width=4) (actual time=0.006..0.011 rows=12 loops=130)
Recheck Cond: (category_id = cc.id)
Filter: public
Rows Removed by Filter: 0
Heap Blocks: exact=525
Buffers: shared hit=791
-> Bitmap Index Scan on answers_exam_category_id_ae65ab1f (cost=0.00..4.38 rows=13 width=0) (actual time=0.003..0.003 rows=12 loops=130)
Index Cond: (category_id = cc.id)
Buffers: shared hit=266
-> Nested Loop (cost=4.86..105.46 rows=21 width=4) (actual time=0.023..0.023 rows=1 loops=1508)
Buffers: shared hit=30948
-> Bitmap Heap Scan on answers_answersection aas (cost=4.58..87.40 rows=37 width=8) (actual time=0.007..0.009 rows=8 loops=1508)
Recheck Cond: (exam_id = ae_1.id)
Heap Blocks: exact=2437
Buffers: shared hit=5630
-> Bitmap Index Scan on answers_answersection_exam_id_750cb327 (cost=0.00..4.57 rows=37 width=0) (actual time=0.005..0.005 rows=36 loops=1508)
Index Cond: (exam_id = ae_1.id)
Buffers: shared hit=3193
-> Index Only Scan using answers_answer_answer_section_id_77cdd7af on answers_answer aa (cost=0.29..0.48 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=12210)
Index Cond: (answer_section_id = aas.id)
Heap Fetches: 817
Buffers: shared hit=25318
SubPlan 3
-> Aggregate (cost=1119.38..1119.39 rows=1 width=8) (actual time=12.574..12.575 rows=1 loops=130)
Buffers: shared hit=55938
-> Hash Join (cost=29.65..1118.66 rows=286 width=0) (actual time=2.483..12.540 rows=266 loops=130)
Hash Cond: (aas_1.exam_id = ae_2.id)
Buffers: shared hit=55938
-> Seq Scan on answers_answersection aas_1 (cost=0.00..995.79 rows=35419 width=4) (actual time=0.005..9.228 rows=35184 loops=122)
Filter: has_answers
Rows Removed by Filter: 18931
Buffers: shared hit=55144
-> Hash (cost=29.49..29.49 rows=13 width=4) (actual time=0.016..0.016 rows=12 loops=130)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=791
-> Bitmap Heap Scan on answers_exam ae_2 (cost=4.38..29.49 rows=13 width=4) (actual time=0.006..0.011 rows=12 loops=130)
Recheck Cond: (category_id = cc.id)
Filter: public
Rows Removed by Filter: 0
Heap Blocks: exact=525
Buffers: shared hit=791
-> Bitmap Index Scan on answers_exam_category_id_ae65ab1f (cost=0.00..4.38 rows=13 width=0) (actual time=0.003..0.003 rows=12 loops=130)
Index Cond: (category_id = cc.id)
Buffers: shared hit=266
SubPlan 4
-> Aggregate (cost=1279.42..1279.43 rows=1 width=8) (actual time=12.886..12.886 rows=1 loops=130)
Buffers: shared hit=149037
-> Nested Loop Semi Join (cost=29.94..1279.10 rows=129 width=0) (actual time=6.119..12.860 rows=184 loops=130)
Buffers: shared hit=149037
-> Hash Join (cost=29.65..1118.66 rows=286 width=4) (actual time=2.398..12.249 rows=266 loops=130)
Hash Cond: (aas_2.exam_id = ae_3.id)
Buffers: shared hit=55935
-> Seq Scan on answers_answersection aas_2 (cost=0.00..995.79 rows=35419 width=8) (actual time=0.005..9.034 rows=35184 loops=122)
Filter: has_answers
Rows Removed by Filter: 18931
Buffers: shared hit=55144
-> Hash (cost=29.49..29.49 rows=13 width=4) (actual time=0.030..0.030 rows=12 loops=130)
Buckets: 1024 Batches: 1 Memory Usage: 8kB
Buffers: shared hit=791
-> Bitmap Heap Scan on answers_exam ae_3 (cost=4.38..29.49 rows=13 width=4) (actual time=0.014..0.022 rows=12 loops=130)
Recheck Cond: (category_id = cc.id)
Filter: public
Rows Removed by Filter: 0
Heap Blocks: exact=525
Buffers: shared hit=791
-> Bitmap Index Scan on answers_exam_category_id_ae65ab1f (cost=0.00..4.38 rows=13 width=0) (actual time=0.008..0.008 rows=12 loops=130)
Index Cond: (category_id = cc.id)
Buffers: shared hit=266
-> Index Only Scan using answers_answer_answer_section_id_77cdd7af on answers_answer aa_1 (cost=0.29..0.57 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=34531)
Index Cond: (answer_section_id = aas_2.id)
Heap Fetches: 23900
Buffers: shared hit=93102
Planning Time: 1.706 ms
Execution Time: 3361.498 ms
(107 rows)Maybe indexing the has_answers field is already enough(?) We might need to get creative otherwise.
Edited by Jacques Hoffmann