基于 PostgreSQL + pgvector 的以图搜图商品搜索系统

┌──────────────────┐     ┌───────────────────────┐     ┌────────────────────┐
│ product_catalog  │     │ product_image_search  │     │ image_search_log   │
│──────────────────│     │───────────────────────│     │────────────────────│
│ product_id (PK)  │◄────│ product_id (FK)       │     │ id (PK)            │
│ product_name     │     │ image_id (UK)         │     │ session_id         │
│ category         │     │ embedding vector(768) │     │ query_vector       │
│ brand            │     │ image_type            │     │ result_count       │
│ price            │     │ angle                 │     │ top_similarity     │
│ tags             │     │ metadata (JSONB)      │     │ search_time_ms     │
└──────────────────┘     └───────────────────────┘     └────────────────────┘

SELECT
    version() AS pg_version,
    current_database() AS database,
    current_user AS user_name,
    pg_size_pretty(pg_database_size(current_database())) AS db_size,
    inet_server_addr() AS server_ip,
    inet_server_port() AS server_port;

SELECT name, default_version, comment
FROM pg_available_extensions
WHERE name IN ('vector', 'age', 'pg_trgm', 'pg_prewarm')
ORDER BY name;

CREATE EXTENSION IF NOT EXISTS vector;

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'vector';

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE EXTENSION IF NOT EXISTS pg_prewarm;

DROP TABLE IF EXISTS product_image_search CASCADE;
DROP TABLE IF EXISTS product_catalog CASCADE;
DROP TABLE IF EXISTS image_search_log CASCADE;

CREATE TABLE product_catalog (
    id              BIGSERIAL PRIMARY KEY,
    product_id      VARCHAR(32) NOT NULL UNIQUE,
    product_name    TEXT NOT NULL,
    category        VARCHAR(100) NOT NULL,
    sub_category    VARCHAR(100),
    brand           VARCHAR(100),
    price           NUMERIC(10, 2),
    description     TEXT,
    tags            TEXT[],
    status          SMALLINT DEFAULT 1,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE product_catalog IS '商品目录表 - 存储商品基本信息';

CREATE TABLE product_image_search (
    id              BIGSERIAL PRIMARY KEY,
    image_id        VARCHAR(64) NOT NULL UNIQUE,
    product_id      VARCHAR(32) NOT NULL REFERENCES product_catalog(product_id),
    image_url       TEXT NOT NULL,
    thumbnail_url   TEXT,
    embedding       vector(768) NOT NULL,
    image_type      VARCHAR(20) DEFAULT 'main',
    angle           VARCHAR(20) DEFAULT 'front',
    background      VARCHAR(20) DEFAULT 'white',
    metadata        JSONB DEFAULT '{}',
    status          SMALLINT DEFAULT 1,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE product_image_search IS '商品图片向量表 - 以图搜图核心表';
COMMENT ON COLUMN product_image_search.embedding IS 'CLIP ViT-L/14 模型生成的 768 维图片特征向量';
COMMENT ON COLUMN product_image_search.image_type IS '图片类型: main=主图, detail=细节图, scene=场景图';
COMMENT ON COLUMN product_image_search.angle IS '拍摄角度: front=正面, side=侧面, back=背面, top=俯视';

CREATE TABLE image_search_log (
    id              BIGSERIAL PRIMARY KEY,
    session_id      VARCHAR(64),
    query_vector    vector(768),
    query_image_url TEXT,
    result_count    INT,
    top_similarity  REAL,
    search_time_ms  REAL,
    filter_category VARCHAR(100),
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE image_search_log IS '以图搜图搜索日志 - 记录搜索行为和性能';

SELECT
    c.relname AS table_name,
    obj_description(c.oid) AS comment,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND c.relname IN ('product_catalog', 'product_image_search', 'image_search_log')
ORDER BY c.relname;

SELECT
    column_name,
    data_type,
    character_maximum_length,
    column_default,
    is_nullable
FROM information_schema.columns
WHERE table_name = 'product_image_search'
  AND table_schema = 'public'
ORDER BY ordinal_position;

INSERT INTO product_catalog (product_id, product_name, category, sub_category, brand, price, description, tags, status)
SELECT
    'P' || lpad(g::text, 6, '0'),
    CASE (g % 8)
        WHEN 0 THEN '智能手机 ' || (ARRAY['Pro','Max','Ultra','Lite','SE','Plus','Mini','Air'])[(g % 8) + 1] || ' ' || g
        WHEN 1 THEN '笔记本电脑 ' || (ARRAY['ThinkPad','MacBook','Surface','MateBook','XPS','Yoga','ZenBook','Swift'])[(g % 8) + 1] || ' ' || g
        WHEN 2 THEN '运动鞋 ' || (ARRAY['Air Max','Ultra Boost','Gel','Fresh Foam','React','Zoom','NB','Asics'])[(g % 8) + 1] || ' ' || g
        WHEN 3 THEN '机械键盘 ' || (ARRAY['Cherry','Filco','Leopold','HHKB','Ducky','Razer','Logitech','Keychron'])[(g % 8) + 1] || ' ' || g
        WHEN 4 THEN '智能手表 ' || (ARRAY['Apple Watch','Galaxy Watch','Huawei Watch','Amazfit','Garmin','Fitbit','TicWatch','Suunto'])[(g % 8) + 1] || ' ' || g
        WHEN 5 THEN '无线耳机 ' || (ARRAY['AirPods','Galaxy Buds','FreeBuds','WF-1000','WH-1000','Bose QC','Jabra','Sony'])[(g % 8) + 1] || ' ' || g
        WHEN 6 THEN '双肩背包 ' || (ARRAY['Osprey','North Face','Arc','Samsonite','Tumi','Incase','Herschel','Peak'])[(g % 8) + 1] || ' ' || g
        ELSE '咖啡机 ' || (ARRAY['Nespresso','De Longhi','Breville','Jura','Saeco','Krups','Moccamaster','Fellow'])[(g % 8) + 1] || ' ' || g
    END,
    (ARRAY['手机数码','电脑办公','运动户外','外设配件','穿戴设备','音频设备','箱包皮具','家用电器'])[(g % 8) + 1],
    -- ... (品牌/价格/描述/标签/状态 自动生成)
FROM generate_series(1, 200) g;

SELECT
    category,
    count(*) AS product_count,
    round(avg(price), 2) AS avg_price,
    min(price) AS min_price,
    max(price) AS max_price
FROM product_catalog
WHERE status = 1
GROUP BY category
ORDER BY product_count DESC;

INSERT INTO product_image_search
    (image_id, product_id, image_url, thumbnail_url, embedding, image_type, angle, background, metadata, status)
SELECT
    'IMG-' || lpad(sub.img_idx::text, 8, '0'),
    sub.pid,
    'https://cdn.example.com/products/' || sub.pid || '/' || sub.itype || '.jpg',
    'https://cdn.example.com/products/' || sub.pid || '/' || sub.itype || '_thumb.jpg',
    -- 为同一商品的图片生成相近的向量（加噪声模拟不同角度）
    (
        SELECT array_agg(
            sin(sub.base_seed + i * 0.01 + sub.noise_offset) * 0.5
            + cos(sub.base_seed * 2 + i * 0.02) * 0.3
            + (random() - 0.5) * sub.noise_factor
        )::vector(768)
        FROM generate_series(1, 768) i
    ),
    sub.itype,
    sub.angle_val,
    (ARRAY['white','transparent','scene','studio'])[(sub.img_idx % 4) + 1],
    jsonb_build_object(
        'width', 800 + (sub.img_idx % 400),
        'height', 800 + (sub.img_idx % 400),
        'format', 'jpg',
        'size_kb', 50 + (sub.img_idx % 200),
        'model', 'CLIP-ViT-L/14',
        'dim', 768
    ),
    1
FROM (
    SELECT
        p.product_id AS pid,
        (p.id - 1) * 3 + t.n AS img_idx,
        CASE t.n WHEN 1 THEN 'main' WHEN 2 THEN 'detail' ELSE 'scene' END AS itype,
        CASE t.n WHEN 1 THEN 'front' WHEN 2 THEN 'side' ELSE 'top' END AS angle_val,
        (p.id * 7.13 + (p.id % 8) * 100) AS base_seed,
        t.n * 0.05 AS noise_offset,
        CASE t.n WHEN 1 THEN 0.02 WHEN 2 THEN 0.05 ELSE 0.08 END AS noise_factor
    FROM product_catalog p
    CROSS JOIN (SELECT generate_series(1, 3) AS n) t
) sub;

SELECT
    pis.image_type,
    count(*) AS image_count,
    count(DISTINCT pis.product_id) AS product_count,
    pg_size_pretty(sum(pg_column_size(pis.embedding))) AS vector_storage
FROM product_image_search pis
WHERE pis.status = 1
GROUP BY pis.image_type
ORDER BY image_count DESC;

ANALYZE product_catalog;
ANALYZE product_image_search;
ANALYZE image_search_log;

CREATE INDEX idx_product_image_embedding_cosine
ON product_image_search
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

-- 商品ID关联索引
CREATE INDEX idx_product_image_product_id ON product_image_search (product_id);

-- 图片类型过滤索引（部分索引）
CREATE INDEX idx_product_image_type ON product_image_search (image_type) WHERE status = 1;

-- 商品分类索引（部分索引）
CREATE INDEX idx_product_catalog_category ON product_catalog (category) WHERE status = 1;

-- 品牌过滤索引
CREATE INDEX idx_product_catalog_brand ON product_catalog (brand) WHERE status = 1;

-- 价格范围索引
CREATE INDEX idx_product_catalog_price ON product_catalog (price) WHERE status = 1;

-- 商品标签 GIN 索引
CREATE INDEX idx_product_catalog_tags ON product_catalog USING GIN (tags) WHERE status = 1;

-- 图片元数据 GIN 索引
CREATE INDEX idx_product_image_metadata ON product_image_search USING GIN (metadata);

-- 搜索日志时间索引
CREATE INDEX idx_search_log_created ON image_search_log (created_at DESC);

SELECT
    indexrelname AS index_name,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND relname IN ('product_image_search', 'product_catalog', 'image_search_log')
ORDER BY pg_relation_size(indexrelid) DESC;

SET hnsw.ef_search = 100;
SET hnsw.iterative_scan = relaxed_order;

WITH query_image AS (
    SELECT embedding
    FROM product_image_search
    WHERE product_id = 'P000001' AND image_type = 'main'
    LIMIT 1
)
SELECT
    pis.image_id,
    pis.product_id,
    pc.product_name,
    pc.category,
    pc.brand,
    pc.price,
    pis.image_type,
    round((1 - (pis.embedding <=> qi.embedding))::numeric, 4) AS similarity
FROM product_image_search pis
CROSS JOIN query_image qi
JOIN product_catalog pc ON pc.product_id = pis.product_id
WHERE pis.status = 1
  AND pis.product_id != 'P000001'
ORDER BY pis.embedding <=> qi.embedding
LIMIT 10;