Câu hỏi mà những bộ đếm không trả lời được

Bạn sẽ tạo ra: Một corpus một triệu tin nhắn, câu truy vấn phân tích lần đầu được viết cho Postgres, và bốn con số cho thấy index lẽ ra phải sửa được nó lại tốn thêm 49% dung lượng mà đổi lại chỉ là nhiễu · khoảng 55 phút, bao gồm bài tập

Two questions, two shapes

// No dedicated (channel_id, sequence DESC) index: DR-01's unique
// constraint above already supplies that ordering, and Postgres walks
// it backward for newest-first pages. Chapter 2.4 measured it and
// migration 0001 dropped the redundant twin (SAD §6.3, amended).

SELECT date_trunc('day', m.created_at) AS day,
       count(*)                        AS messages,
       count(DISTINCT m.user_id)       AS active_users
FROM messages m JOIN channels c ON c.id = m.channel_id
WHERE c.environment_id = $1
  AND m.created_at >= now() - interval '90 days'
GROUP BY 1;

A corpus worth measuring against

created.messages            1,600,000   every row in the database
  environment_id = $1       1,333,334   the tenant predicate excludes the neighbours
    >= now() - 90 days      1,000,000   the date predicate excludes a quarter

What it costs, and what it costs the neighbour

GroupAggregate  (actual time=609.731..698.486)
  ->  Sort  (actual time=608.870..656.611)
        Sort Method: external merge  Disk: 33312kB
        ->  Hash Join  (actual time=6.750..277.774)
              ->  Seq Scan on messages m  (actual time=6.383..140)
              ->  Seq Scan on channels c  (actual time=0.017..0.142)

send p95, alone           20.5 ms      mean of two control loops
send p95, beside          13.7 ms      102 analytical queries over the window
NFR-PRF-02's target      150 ms

The index that should fix it

ALTER TABLE messages ADD COLUMN environment_id uuid; UPDATE messages m SET environment_id = c.environment_id FROM channels c WHERE c.id = m.channel_id; CREATE INDEX ON messages (environment_id, created_at);

                    run 1     run 2     run 3
baseline            585.9     603.4         -
with the index      560.2         -     552.0

And what it costs to keep

table before the column        178.6 MB
after ALTER + backfill         381.9 MB
after VACUUM FULL              203.4 MB

the column                      24.8 MB   permanent
the index                       62.0 MB   permanent
the rewrite                    178.6 MB   transient, reclaimed

What the lane would have said

                        rows in environment    query      days returned
lane, busiest env                    1,018     0.9 ms                 1
the corpus                       1,000,000   585.9 ms                91