Início / dba / Perguntas / 217672
Accepted
Dmitry K.
Asked: 2018-09-15 16:16:07 +0800 CST

PostgreSQL como acelerar ORDER BY com coluna de texto

  • 772

Esquema da tabela:

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;

A tabela "fsa_online" contém cerca de 800.000 registros (possível crescer até 3 milhões de registros)

A tabela "fsa_online_data" contém cerca de 3,5 milhões de registros (possível crescer até 15-25 milhões de registros)

Tenho a seguinte consulta:

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

Demorou cerca de 150 ms.

Mas quando preciso classificar dados, por exemplo, pela coluna "cert_num" da tabela "fsa_online_data", demorava muito (cerca de 63.000 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

Eu fiz um pequeno truque adicionando COLLATE "C" (mas isso não está correto, eu realmente preciso comparar strings como strings Unicode) à instrução ORDER BY e a consulta levou cerca de 27500 ms.

Aqui está um plano de consulta:

"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)"

Além disso, preciso realizar consultas usando instruções LIKE em diferentes colunas de texto (a pesquisa de texto completo não é boa para este caso, porque as colunas de texto contêm dados arbitrários, como números de série).

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

O resultado é de cerca de 900.000 registros. O tamanho da tabela "fsa_online_data" é 7 GB.

Lista de hardware:

  • Samsung SSD EVO 850
  • Intel Core i7 6700k
  • 16 GB de RAM DDR4

Testado nas versões do PostgreSQL: 9.6.9 e 10.5

Como posso melhorar o desempenho das operações ORDER BY por colunas de texto? Quero reduzir o tempo de execução da consulta para 200-300ms.

postgresql

2 respostas

  1. Best Answer

    O CASE em uma condição de junção está matando você.

    Você provavelmente deve reorganizar seus dados para que use_version sempre contenha a versão a ser usada. Se você não puder fazer isso, crie um índice de expressão na expressão CASE:

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

  2. Olhando para a declaração do seu caso,

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

    Isso deveria ser, em vez disso,

    coalesce(use_version, last_original_version);

    Você provavelmente pode torná-lo mais rápido fazendo algo assim (estúpido "removido, conforme a convenção)

    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;
    • 1

relate perguntas