mirror of
https://gh.wpcy.net/https://github.com/discourse/discourse.git
synced 2026-06-19 03:23:50 +08:00
6b7cebdcb0
Wraps the trust-level `action` filter in a `MATERIALIZED` CTE so Postgres resolves those (rare) rows first via the existing `index_staff_action_logs_on_action_and_id`, instead of mis-estimating the unestim-able regex filters and falling back to a full-table merge join over `target_user_id`... Same results, no new index and ~11× fewer buffers on the 1-year range (from 230k to 20k). | Range | baseline buffers | rewrite buffers | baseline cold | rewrite cold | |---|---:|---:|---:|---:| | 7 day | 12.4k | 12.4k | 30 ms | 32 ms | | 30 day | 12.8k | 12.8k | 29 ms | 32 ms | | 3 month | 231k | **14k** | 103 ms | **37 ms** (2.8×) | | 1 year | 231k | **20k** | 96 ms | **40 ms** (2.4×) | The better improvement would be to add an index on the user_history table. But I think we can avoid it for now.
105 lines
3.1 KiB
Ruby
Vendored
105 lines
3.1 KiB
Ruby
Vendored
# frozen_string_literal: true
|
|
|
|
module Reports::TrustLevelPipeline
|
|
extend ActiveSupport::Concern
|
|
|
|
class_methods do
|
|
def report_trust_level_pipeline(report)
|
|
report.modes = [Report::MODES[:table]]
|
|
report.labels = [
|
|
{ property: :name, title: I18n.t("reports.trust_level_pipeline.labels.level") },
|
|
{
|
|
property: :count,
|
|
type: :number,
|
|
title: I18n.t("reports.trust_level_pipeline.labels.count"),
|
|
},
|
|
{ property: :share_formatted, title: I18n.t("reports.trust_level_pipeline.labels.share") },
|
|
{
|
|
property: :moves_in,
|
|
type: :number,
|
|
title: I18n.t("reports.trust_level_pipeline.labels.moves_in"),
|
|
},
|
|
{
|
|
property: :moves_out,
|
|
type: :number,
|
|
title: I18n.t("reports.trust_level_pipeline.labels.moves_out"),
|
|
},
|
|
]
|
|
|
|
snapshot = User.real.group(:trust_level).count
|
|
total_members = snapshot.values.sum
|
|
|
|
moves_in_by_tl = Hash.new(0)
|
|
moves_out_by_tl = Hash.new(0)
|
|
total_up = 0
|
|
total_down = 0
|
|
|
|
moves_sql = <<~SQL
|
|
WITH trust_changes AS MATERIALIZED (
|
|
SELECT target_user_id, new_value, previous_value, created_at
|
|
FROM user_histories
|
|
WHERE action IN (:change_action, :auto_action)
|
|
)
|
|
SELECT
|
|
new_value::integer AS new_tl,
|
|
previous_value::integer AS prev_tl,
|
|
COUNT(*) AS move_count
|
|
FROM trust_changes
|
|
WHERE created_at >= :start_date
|
|
AND created_at <= :end_date
|
|
AND previous_value ~ '^\\d+$'
|
|
AND new_value ~ '^\\d+$'
|
|
AND target_user_id IN (SELECT id FROM users WHERE id > 0)
|
|
GROUP BY new_value::integer, previous_value::integer
|
|
SQL
|
|
|
|
DB
|
|
.query(
|
|
moves_sql,
|
|
change_action: UserHistory.actions[:change_trust_level],
|
|
auto_action: UserHistory.actions[:auto_trust_level_change],
|
|
start_date: report.start_date,
|
|
end_date: report.end_date,
|
|
)
|
|
.each do |row|
|
|
next if row.new_tl == row.prev_tl
|
|
moves_in_by_tl[row.new_tl] += row.move_count
|
|
moves_out_by_tl[row.prev_tl] += row.move_count
|
|
if row.new_tl > row.prev_tl
|
|
total_up += row.move_count
|
|
else
|
|
total_down += row.move_count
|
|
end
|
|
end
|
|
|
|
report.data =
|
|
TrustLevel.valid_range.to_a.reverse.map do |tl|
|
|
count = snapshot.fetch(tl, 0)
|
|
share = total_members.zero? ? 0.0 : (count.to_f / total_members * 100).round(2)
|
|
{
|
|
trust_level: tl,
|
|
name: I18n.t("reports.trust_level_pipeline.levels.#{tl}"),
|
|
count: count,
|
|
share: share,
|
|
share_formatted: "#{share}%",
|
|
moves_in: moves_in_by_tl[tl],
|
|
moves_out: moves_out_by_tl[tl],
|
|
}
|
|
end
|
|
|
|
net = total_up - total_down
|
|
|
|
direction =
|
|
if net > 0
|
|
"climbing"
|
|
elsif net < 0
|
|
"dropping"
|
|
else
|
|
"stable"
|
|
end
|
|
|
|
report.total = total_members
|
|
report.prev_period = { direction: direction, net: net.abs }
|
|
end
|
|
end
|
|
end
|
