PostgreSQL如何用文本列加速排序

Question

表模式：

CREATE SEQUENCE fsa_online_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;

CREATE TABLE "public"."fsa_online" (
    "id" integer DEFAULT nextval('fsa_online_id_seq') NOT NULL,
    "fsa_uuid" uuid NOT NULL,
    "use_version" integer,
    "last_original_version" integer,
    "created_at" timestamp(0),
    "updated_at" timestamp(0),
    "deleted_at" timestamp(0),
    "is_drug" boolean DEFAULT true NOT NULL,
    CONSTRAINT "fsa_online_fsa_uuid_unique" UNIQUE ("fsa_uuid"),
    CONSTRAINT "fsa_online_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

CREATE INDEX "fsa_online_is_drug_index" ON "public"."fsa_online" USING btree ("is_drug");
CREATE INDEX "fsa_online_last_original_version_index" ON "public"."fsa_online" USING btree ("last_original_version");
CREATE INDEX "fsa_online_use_version_index" ON "public"."fsa_online" USING btree ("use_version");


CREATE SEQUENCE fsa_online_data_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;

CREATE TABLE "public"."fsa_online_data" (
    "id" integer DEFAULT nextval('fsa_online_data_id_seq') NOT NULL,
    "fsa_id" integer NOT NULL,
    "reason" text,
    "is_original" boolean NOT NULL,
    "is_published" boolean DEFAULT true NOT NULL,
    "created_by_id" integer,
    "created_at" timestamp(0),
    "unparsed_data" jsonb,
    "raw_id" integer NOT NULL,
    "status_id" integer,
    "type_id" integer,
    "reg_num" character varying(255),
    "start_date" date,
    "end_date" date,
    "docs" jsonb,
    "docs_add" text,
    "scheme" text,
    "free_form" jsonb,
    "fio_expert" text,
    "lab_info" jsonb,
    "change_info" jsonb,
    "applicant_info" jsonb,
    "manufacturer_info" jsonb,
    "product_info" jsonb,
    "standard_info" jsonb,
    "manufacturer_tbl_info" jsonb,
    "product_tbl_info" jsonb,
    "certification_info" jsonb,
    "trade_name" text,
    "cert_num" character varying(255),
    "man_form_txt" text,
    "manufacturer_name" text,
    "man_country_id" integer,
    "serial_num" character varying(255),
    "serial_size" integer,
    "barcode" character varying(255),
    "barcode_type_id" integer,
    "is_cert" boolean NOT NULL,
    "original_data" jsonb,
    CONSTRAINT "fsa_online_data_pkey" PRIMARY KEY ("id"),
    CONSTRAINT "fsa_online_data_fsa_id_foreign" FOREIGN KEY (fsa_id) REFERENCES fsa_online(id) ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE
) WITH (oids = false);

CREATE INDEX "fsa_online_data_barcode_index" ON "public"."fsa_online_data" USING btree ("barcode");
CREATE INDEX "fsa_online_data_barcode_type_id_index" ON "public"."fsa_online_data" USING btree ("barcode_type_id");
CREATE INDEX "fsa_online_data_cert_num_index" ON "public"."fsa_online_data" USING btree ("cert_num");
CREATE INDEX "fsa_online_data_created_by_id_index" ON "public"."fsa_online_data" USING btree ("created_by_id");
CREATE INDEX "fsa_online_data_end_date_index" ON "public"."fsa_online_data" USING btree ("end_date");
CREATE INDEX "fsa_online_data_fsa_id_index" ON "public"."fsa_online_data" USING btree ("fsa_id");
CREATE INDEX "fsa_online_data_is_cert_index" ON "public"."fsa_online_data" USING btree ("is_cert");
CREATE INDEX "fsa_online_data_man_country_id_index" ON "public"."fsa_online_data" USING btree ("man_country_id");
CREATE INDEX "fsa_online_data_raw_id_index" ON "public"."fsa_online_data" USING btree ("raw_id");
CREATE INDEX "fsa_online_data_reg_num_index" ON "public"."fsa_online_data" USING btree ("reg_num");
CREATE INDEX "fsa_online_data_start_date_index" ON "public"."fsa_online_data" USING btree ("start_date");
CREATE INDEX "fsa_online_data_status_id_index" ON "public"."fsa_online_data" USING btree ("status_id");
CREATE INDEX "fsa_online_data_trade_name_index" ON "public"."fsa_online_data" USING btree ("trade_name");
CREATE INDEX "fsa_online_data_type_id_index" ON "public"."fsa_online_data" USING btree ("type_id");

ALTER TABLE "fsa_online" ADD CONSTRAINT "fsa_online_last_original_version_foreign" FOREIGN KEY (last_original_version) REFERENCES fsa_online_data(id) ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE;
ALTER TABLE "fsa_online" ADD CONSTRAINT "fsa_online_use_version_foreign" FOREIGN KEY (use_version) REFERENCES fsa_online_data(id) ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE;

"fsa_online“表包含约80万条记录(可能增长到300万条记录)

"fsa_online_data“表包含约350万条记录(可能增长到1500万至2500万条记录)。

我有以下查询：

select 
  "fsa_online"."id", 
  "fsa_online"."fsa_uuid", 
  "fsa_online"."use_version", 
  "fsa_online"."last_original_version", 
  "fsa_online"."is_drug" 
from 
  "fsa_online" 
  inner join "fsa_online_data" as "data" on "data"."id" = CASE WHEN fsa_online.use_version IS NULL THEN fsa_online.last_original_version ELSE fsa_online.use_version END 
where 
  "unparsed_data" is not null 
  and "fsa_online"."deleted_at" is null 
limit 10 offset 0

花费了大约150毫秒的时间。

但是，当我需要对数据进行排序时(例如，通过"cert_num“表中的"fsa_online_data”列)，需要花费非常长的时间(大约63000 ms)。

select 
  "fsa_online"."id", 
  "fsa_online"."fsa_uuid", 
  "fsa_online"."use_version", 
  "fsa_online"."last_original_version", 
  "fsa_online"."is_drug" 
from 
  "fsa_online" 
  inner join "fsa_online_data" as "data" on "data"."id" = CASE WHEN fsa_online.use_version IS NULL THEN fsa_online.last_original_version ELSE fsa_online.use_version END 
where 
  "unparsed_data" is not null 
  and "fsa_online"."deleted_at" is null 
ORDER BY "data"."cert_num"
limit 10 offset 0

我通过添加排序规则"C“(但这是不正确的，我真的需要比较字符串作为Unicode字符串)来按语句排序，查询花费了大约27500毫秒。

下面是一个查询计划：

"Limit  (cost=1037857.75..1037857.78 rows=10 width=545)"
"  Output: fsa_online.id, fsa_online.fsa_uuid, fsa_online.use_version, fsa_online.last_original_version, fsa_online.is_drug, ((data.cert_num)::character varying(255))"
"  ->  Sort  (cost=1037857.75..1039085.55 rows=491120 width=545)"
"        Output: fsa_online.id, fsa_online.fsa_uuid, fsa_online.use_version, fsa_online.last_original_version, fsa_online.is_drug, ((data.cert_num)::character varying(255))"
"        Sort Key: ((data.cert_num)::character varying(255)) COLLATE "C""
"        ->  Hash Join  (cost=972267.98..1027244.83 rows=491120 width=545)"
"              Output: fsa_online.id, fsa_online.fsa_uuid, fsa_online.use_version, fsa_online.last_original_version, fsa_online.is_drug, data.cert_num"
"              Inner Unique: true"
"              Hash Cond: (CASE WHEN (fsa_online.use_version IS NULL) THEN fsa_online.last_original_version ELSE fsa_online.use_version END = data.id)"
"              ->  Seq Scan on public.fsa_online  (cost=0.00..19143.06 rows=899706 width=29)"
"                    Output: fsa_online.id, fsa_online.fsa_uuid, fsa_online.use_version, fsa_online.last_original_version, fsa_online.is_drug"
"                    Filter: (fsa_online.deleted_at IS NULL)"
"              ->  Hash  (cost=934126.84..934126.84 rows=2077451 width=17)"
"                    Output: data.cert_num, data.id"
"                    ->  Seq Scan on public.fsa_online_data data  (cost=0.00..934126.84 rows=2077451 width=17)"
"                          Output: data.cert_num, data.id"
"                          Filter: (data.unparsed_data IS NOT NULL)"

另外，我需要在不同的文本列上使用LIKE语句来执行查询(对于这种情况，全文搜索并不好，因为文本列包含任意数据，比如序列号)。

select count(*)
from "fsa_online" 
inner join "fsa_online_data" as "data" on "data"."id" = 
CASE WHEN fsa_online.use_version IS NULL THEN fsa_online.last_original_version ELSE fsa_online.use_version END 

其结果是创造了约90万项记录。"fsa_online_data“表大小为7GB。

硬件列表：

• 三星SSD EVO 850
• 英特尔核心i7 6700k
• 16 GB DDR4内存

在PostgreSQL版本上测试: 9.6.9和10.5

如何通过文本列的操作来提高ORDER的性能？我希望将查询执行时间缩短到200到300 to。

Answer 1

加入状态下的情况是要你的命。

您可能应该重组您的数据，以便use_version始终包含要使用的版本。如果不能这样做，那么在CASE表达式上创建一个表达式索引：

create index on fsa_online ((CASE WHEN use_version IS NULL 
    THEN last_original_version ELSE use_version END));

Answer 2

看看你的案情陈述，

CASE WHEN fsa_online.use_version IS NULL THEN fsa_online.last_original_version ELSE fsa_online.use_version END 

取而代之的是，

coalesce(use_version, last_original_version);

您可能可以通过这样做来使它更快，(按照惯例，愚蠢的"删除了)

SELECT 
  fsa_online.id, 
  fsa_online.fsa_uuid, 
  fsa_online.use_version, 
  fsa_online.last_original_version, 
  fsa_online.is_drug 
FROM fsa_online_data AS data
CROSS JOIN LATERAL (
  SELECT *
  FROM fsa_online
  WHERE data.id = coalesce(use_version, last_original_version)
    AND fsa_online.deleted_at IS NULL 
) AS fsa_online
WHERE data.unparsed_data IS NOT NULL
ORDER BY cert_num
FETCH FIRST 10 ROWS ONLY;