postgresql - 为什么在我的案例中没有使用这个 GIN 索引 (jsonb)

Question

我的代码在这里https://rextester.com/CBYD42261 ，我使用 '@>' 和 gin 索引进行搜索，但 postgresql 使用 Seq Scan。

DROP TABLE IF EXISTS sponsor_projects;

CREATE TABLE sponsor_projects (
    project_id BIGSERIAL PRIMARY KEY,
    sponsor_id bigint NOT NULL,
    status smallint NOT NULL DEFAULT 0,
    name character varying(64) NOT NULL,
    category character varying(10) NOT NULL,
    purpose jsonb NOT NULL,
    qrcode character varying(200) NOT NULL,
    plaform character varying(10) NOT NULL,
    budget double precision NOT NULL,
    budget_used double precision NOT NULL,
    sticker character varying(10) NOT NULL,
    spread character varying(10) NOT NULL,
    areas jsonb NOT NULL,
    paused boolean DEFAULT false,
    terminated boolean DEFAULT false,
    start_time timestamp(0) without time zone NOT NULL,
    end_time timestamp(0) without time zone,
    shops jsonb NOT NULL,
    created_by integer,
    created_at timestamp(0) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp(0) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted boolean DEFAULT false,
    deleted_at timestamp(0) without time zone DEFAULT NULL::timestamp without time zone
);

-- Indices -------------------------------------------------------

CREATE INDEX idx_sponsor_projects_sponsor_id ON sponsor_projects(sponsor_id int8_ops);
CREATE INDEX idx_sponsor_projects ON sponsor_projects USING GIN (shops);


-- Insert Data ---------------------------------------------------
INSERT INTO "sponsor_projects"("sponsor_id","status","name","category","purpose","qrcode","plaform","budget","budget_used","sticker","spread","areas","paused","terminated","start_time","end_time","shops","created_by","created_at","updated_at","deleted","deleted_at")
VALUES
(1,0,E'京东广告',E'3C数码产品',E'["品牌宣传", "流量拉新"]',E'https://jd.com',E'不限',1000,0,E'标准桌贴',E'CPC',E'[{"id": "fc5d1d71-14b1-473d-9db2-c804b0d9ab6c", "path": [[116.68767, 39.877689], [116.712303, 39.868862], [116.704149, 39.8601], [116.6887, 39.865041]]}]',FALSE,FALSE,E'2019-08-20 06:51:26',NULL,E'[{"shop_id": 5}]',NULL,E'2019-08-21 14:55:01',E'2019-08-21 14:55:01',FALSE,NULL);

EXPLAIN ANALYZE
SELECT * FROM sponsor_projects t WHERE t.shops @> '[{"shop_id": 5}]';

显示:

Seq Scan on sponsor_projects t  (cost=0.00..11.00 rows=1 width=893) (actual time=0.013..0.016 rows=1 loops=1)
  Filter: (shops @> '[{"shop_id": 1}]'::jsonb)
  Rows Removed by Filter: 8
Planning Time: 0.076 ms
Execution Time: 0.033 ms

使用序列扫描，而不是 gin 索引。

我的代码怎么了？

有人能帮帮我吗？

Answer 1

那是因为表只包含一行，对于像这样的小表，顺序扫描总是更快。

要测试索引是否可以使用，请不要在数据库 session 中使用顺序扫描:

SET enable_seqscan = off;

然后 PostgreSQL 将尽可能使用该索引。

确保之后重置设置:

RESET enable_seqscan;