TransWikia.com

Как правильно создать индекс покрытия для postgresql

Stack Overflow на русском Asked by Ratatuy 129 on January 8, 2021

Создаю таблицы в PostgreSql, столкнулся с проблемой, когда индекс вроде бы задан верно, но EXPLAN проходит как-будто без него, Seq Scan

Таблица

CREATE TABLE public.tasks
(
    task_id smallint NOT NULL DEFAULT nextval('tasks_task_id_seq'::regclass),
    task_type smallint NOT NULL DEFAULT 4,
    task character varying COLLATE pg_catalog."default" NOT NULL,
    user_id integer NOT NULL,
    completed boolean NOT NULL DEFAULT false,
    overdue boolean NOT NULL DEFAULT false,
    date_add date NOT NULL DEFAULT date(now()),
    CONSTRAINT tasks_pkey PRIMARY KEY (task_id),
    CONSTRAINT tasks_task_type_fkey FOREIGN KEY (task_type)
        REFERENCES public.tasktypes (task_type) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT tasks_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public.users (user_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

Сам индекс

CREATE INDEX ix_tasks_type_user_completed
    ON public.tasks USING btree
    (task_type ASC NULLS LAST, user_id ASC NULLS LAST, completed ASC NULLS LAST, overdue ASC NULLS LAST)
    INCLUDE(task_id, date_add, task)

И запросы

EXPLAIN (ANALYZE)
UPDATE Tasks
SET Overdue = 
    CASE WHEN (task_type = 0 AND date(now()) > date(date_add + INTERVAL'1 day'))
    THEN true
    ELSE false
    END
WHERE overdue = false AND user_id = 123123 AND completed = false
EXPLAIN (ANALYZE)
UPDATE Tasks
SET completed = true
WHERE user_id = 123123 AND task_id IN (1, 2, 3)

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP