sunfmin / notebook

My Note Book
2 stars 0 forks source link

PostgreSQL text column as where conditions query speed not consistent, sometimes up to 300ms #46

Open sunfmin opened 5 years ago

sunfmin commented 5 years ago
create table promotion_benefit_records
(
    id          serial not null
        constraint promotion_benefit_records_pkey
        primary key,
    created_at  timestamp with time zone,
    updated_at  timestamp with time zone,
    deleted_at  timestamp with time zone,
    model_type  text,
    model_id    text,
    discount_id integer,
    benefit_id  integer,
    coupon_id   integer,
    user_id     text
);

create index promotion_benefit_records_model_type_model_id_discount_id_benef
    on promotion_benefit_records (model_type, model_id, discount_id, benefit_id)
    where (deleted_at IS NULL);

VS

create table promotion_benefit_records
(
    id          serial not null
...
    model_type  varchar(50),
    model_id    varchar(50),
...
);

The Query:

SELECT * FROM "promotion_benefit_records"  WHERE "promotion_benefit_records"."deleted_at" IS NULL AND (("promotion_benefit_records"."model_type" = $1) AND ("promotion_benefit_records"."model_id" = $2) AND ("promotion_benefit_records"."discount_id" = $3) AND ("promotion_benefit_records"."benefit_id" = $4)) ORDER BY "promotion_benefit_records"."id" ASC LIMIT 1

After I changed those where condition column type to varchar(50), It becomes fast