Какой столбец должен быть проиндексирован?
У меня есть следующий запрос, который будет попадать в базу данных каждую минуту
SELECT n.notificationid ,n.notificationtype
FROM notification n
INNER JOIN usernotification un ON n.notificationid = un.notificationid
LEFT JOIN vhctotal vt ON vt.vhcid = n.vhcid
WHERE CASE WHEN n.notificationtype = 2 THEN n.datechecked = '' ELSE to_timestamp(n.datechecked, 'DD/MM/YYYY hh24:mi:ss') > NOW() - INTERVAL '30 days' END
AND date_trunc('day', n.vhcdate::TIMESTAMP) = CURRENT_DATE
AND un.commentstatus = 0
AND un.userid = 30427
OR (
un.commentstatus = 1
AND un.updateddatetime > current_timestamp - interval '5 minutes'
)
Вышеприведенный запрос занимает немного много времени для выполнения. когда я проверяю эти таблицы, индексация не выполняется должным образом.
Я хотел бы создать индексы для этих таблиц. Должен ли я создавать индекс для столбцов, которые используются в предложении WHERE? Дает ли это лучшую производительность ? Пожалуйста, предложите .
Что я уже пробовал:
ПЛАН ЗАПРОСА
Nested Loop Left Join (cost=25.96..2136.39 rows=1 width=237) (actual time=49.911..49.911 rows=0 loops=1)
Join Filter: (vt.vhcid = n.vhcid)
-> Hash Join (cost=25.96..2089.81 rows=1 width=163) (actual time=49.908..49.908 rows=0 loops=1)
Hash Cond: (un.notificationid = n.notificationid)
Join Filter: ((CASE WHEN ((n.notificationtype)::text = '2'::text) THEN ((n.datechecked)::text = ''::text) ELSE (to_timestamp((n.datechecked)::text, 'DD/MM/YYYY hh24:mi:ss'::text) > (now() - '30 days'::interval)) END AND (date_trunc('day'::text, (n.vhcdate)::timestamp without time zone) = ('now'::cstring)::date) AND (un.commentstatus = 0) AND (un.userid = 30427)) OR ((un.commentstatus = 1) AND (un.updateddatetime > (now() - '00:05:00'::interval))))
Rows Removed by Join Filter: 26406
-> Seq Scan on usernotification un (cost=0.00..528.36 rows=26136 width=36) (actual time=0.009..3.937 rows=26406 loops=1)
-> Hash (cost=18.76..18.76 rows=576 width=127) (actual time=0.420..0.420 rows=577 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 96kB
-> Seq Scan on notification n (cost=0.00..18.76 rows=576 width=127) (actual time=0.004..0.105 rows=577 loops=1)
-> Seq Scan on vhctotal vt (cost=0.00..33.48 rows=1048 width=74) (never executed)
Total runtime: 50.033 ms
Manoj Kumar Choubey
Я не могу дать вам идеальный ответ без вашей структуры базы данных, но первое, что вам нужно сделать, это проверить, что ваша база данных нормализована и создать первичные ключи и / или внешние ключи по мере необходимости, например, "notificationid" может быть первичным ключом вашей таблицы.
во-вторых, вы пробуете какой-то план запроса, попробуйте сделать это в query profiler и query analyzer.
Richard Deeming
to_timestamp(n.datechecked, 'DD/MM/YYYY hh24:mi:ss')
Храните ли Вы значения даты и времени в строковом столбце? Если так, то это очевидная вещь, которую нужно исправить.
date_trunc('day', n.vhcdate::TIMESTAMP) = CURRENT_DATE
Как правило, следует избегать выполнения вычислений по столбцам таблицы в
WHERE предложение, так как оно не позволит вашему запросу использовать индекс в этом столбце. Вместо того чтобы усекать время и сравнивать результат с датой, проверьте, является ли столбец больше или равен дате и меньше одного дня после даты:AND n.vhcdate >= CURRENT_DATE AND n.vhcdate < CURRENT_DATE + INTERVAL '1 day'
King Fisher
вот у ее был написан такой,
King Fisher
спасибо , но эта колонка была создана как varchar , я не могу использовать это условие AND n.vhcdate >= CURRENT_DATE AND n.vhcdate < CURRENT_DATE + INTERVAL '1 day', Он показывает " оператор не существует: переменный символ = дата"
Richard Deeming
Ну, вот и очевидное место для начала! :)
Вы должны никогда храните даты в виде строк. Вместо этого всегда используйте один из доступных типов даты и времени.
Это не только ускорит ваши запросы, но и предотвратит хранение недопустимых или неоднозначных данных в столбце.