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)
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP