zetaloop
36 lines
1.4 KiB
SQL
36 lines
1.4 KiB
SQL
CREATE TABLE reviews
|
|
(
|
|
id BIGINT PRIMARY KEY,
|
|
order_id BIGINT NOT NULL,
|
|
from_user_id BIGINT NOT NULL,
|
|
from_user_name VARCHAR(100) NOT NULL,
|
|
from_user_avatar TEXT,
|
|
to_user_id BIGINT NOT NULL,
|
|
rating SMALLINT NOT NULL,
|
|
content TEXT,
|
|
sealed BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
unsealed_at TIMESTAMPTZ,
|
|
CONSTRAINT chk_rating_range CHECK (rating >= 1 AND rating <= 5),
|
|
UNIQUE (order_id, from_user_id)
|
|
);
|
|
|
|
-- 基础索引
|
|
CREATE INDEX idx_reviews_order ON reviews (order_id);
|
|
CREATE INDEX idx_reviews_to_user ON reviews (to_user_id, created_at DESC)
|
|
WHERE sealed = FALSE;
|
|
CREATE INDEX idx_reviews_from_user ON reviews (from_user_id);
|
|
|
|
-- 三元组索引用于评价内容搜索
|
|
CREATE INDEX idx_reviews_content_trgm ON reviews USING gin (content gin_trgm_ops)
|
|
WHERE sealed = FALSE AND content IS NOT NULL;
|
|
|
|
-- 复合索引优化用户评价列表
|
|
CREATE INDEX idx_reviews_to_user_rating ON reviews (to_user_id, rating DESC, created_at DESC)
|
|
WHERE sealed = FALSE;
|
|
|
|
-- 复合索引优化订单评价查询
|
|
CREATE INDEX idx_reviews_order_sealed ON reviews (order_id, sealed);
|
|
|
|
-- 评分统计索引
|
|
CREATE INDEX idx_reviews_rating_created ON reviews (rating, created_at DESC) WHERE sealed = FALSE; |