我有3个主要模型，可能有数千个组件和测试记录。
class Component
has_many :test_records, as: :testable
end
class TestRecord
belongs_to :testable, polymorphic: true
belongs_to :test_form
end
class TestForm
has_many :test_records
end
测试记录是在测试可测试项目后生成的，它们会导致通过或失败，并存储为布尔
test_record.passed
我想做的是提取每个组件的最新测试记录，并确定它是否通过。我首先检查它是否已经在以下范围内进行了测试：
scope :tested, ->(test_form_id) {
where("test_records.created_at > ?", 45.days.ago)
.order(created_at: :desc)
.distinct
.joins(:test_records)
.where("test_records.test_form_id = ?", test_form_id)
.active
}
然后我把它传给：
scope :passed, ->(test_form_id, type) {
tested(test_form_id)
.includes(:test_records)
.where('test_records.test_form_id = ?', test_form_id)
.where("test_records.created_at = (
SELECT MAX(test_records.created_at)
FROM test_records
WHERE test_records.testable_id = #{type}.id)")
.where("tested_records.passed = none")
}
此查询在生产中运行需要20秒。
我尝试过以下方法：
scope :passed, ->(test_form_id, type) {
tested(test_form_id)
.distinct('ON (test_records.testable_id)')
.order('test_records.created_at DESC')
.where('test_records.test_form_id = ?', test_form_id)
.where('test_records.passed = ?', true)
.includes(:test_records)
}
但它没有给出正确的结果，因为它在非重复调用之前过滤掉了一个通过的==false测试记录。
TLDR：我有已经测试过的组件，如果test_record.passed==true，我想选择为特定test_form创建的最后一个test_record；目前，查询的时间比我希望的要长40倍，如果能帮我清理活动记录查询，我将不胜感激
谢谢！
编辑：以下是PG解释分析结果：
Unique (cost=1155.71..1155.76 rows=1 width=150) (actual time=8504.313..8504.326 rows=2 loops=1)
-> Sort (cost=1155.71..1155.72 rows=1 width=150) (actual time=8504.312..8504.317 rows=2 loops=1)
Sort Key: components.created_at DESC, components.id, components.barcode, components.description, components.make, components.model, components.system_id, components.updated_at, components.active, components.component_type, components.abbreviation, components.address, test_records.id, test_records.user_id, test_records.passed, test_records.created_at, test_records.updated_at, test_records.manufacturer_test_form_id
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=798.86..1155.70 rows=1 width=150) (actual time=8502.399..8504.226 rows=2 loops=1)
Hash Cond: ((test_records.testable_id = components.id) AND (test_records.created_at = (SubPlan 1)))
-> Hash Join (cost=42.09..243.44 rows=9 width=70) (actual time=1.000..1.010 rows=4 loops=1)
Hash Cond: (test_records.testable_id = components_systems.component_id)
-> Bitmap Heap Scan on test_records (cost=19.85..218.41 rows=718 width=62) (actual time=0.409..0.718 rows=418 loops=1)
Recheck Cond: (test_form_id = 14)
Filter: (passed AND (created_at > '2020-11-03 02:49:31.234492'::timestamp without time zone) AND ((testable_type)::text = 'Component'::text))
Rows Removed by Filter: 1106
Heap Blocks: exact=92
-> Bitmap Index Scan on index_test_records_on_test_form_id (cost=0.00..19.67 rows=1518 width=0) (actual time=0.072..0.073 rows=1525 loops=1)
Index Cond: (test_form_id = 14)
-> Hash (cost=16.43..16.43 rows=465 width=8) (actual time=0.214..0.215 rows=478 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 27kB
-> Index Only Scan using index_components_systems_on_system_id_and_component_id on components_systems (cost=0.29..16.43 rows=465 width=8) (actual time=0.039..0.119 rows=478 loops=1)
Index Cond: (system_id = 2747)
Heap Fetches: 0
-> Hash (cost=704.23..704.23 rows=3503 width=88) (actual time=8499.537..8499.538 rows=1509 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 246kB
-> Bitmap Heap Scan on components (cost=43.81..704.23 rows=3503 width=88) (actual time=0.197..3.433 rows=3478 loops=1)
Recheck Cond: (component_profile_id = 13)
Filter: (active AND active)
Rows Removed by Filter: 22
Heap Blocks: exact=235
-> Bitmap Index Scan on index_components_on_component_profile_id (cost=0.00..42.94 rows=3553 width=0) (actual time=0.165..0.165 rows=3695 loops=1)
Index Cond: (component_profile_id = 13)
SubPlan 1
-> Aggregate (cost=345.43..345.44 rows=1 width=8) (actual time=2.440..2.440 rows=1 loops=3480)
-> Seq Scan on test_records test_records_1 (cost=0.00..345.43 rows=1 width=8) (actual time=1.775..2.437 rows=0 loops=3480)
Filter: (testable_id = components.id)
Rows Removed by Filter: 13897
Planning Time: 0.794 ms
Execution Time: 8504.505 ms
这个解决方案真的比我想象的要简单。我在下面添加了一个迁移：
add_index :test_records, [:testable_id, :testable_type]
之后，查询下降到1.010毫秒