Nullified's questions

创建索引的最佳做法是什么？

示例 1：

created_at_indx (created_at)

updated_at_index (更新时间)

或者

示例 2：

date_indx (created_at, updated_at)

这是查询示例

放置索引需要考虑哪一列？每个索引可以应用每列或多列的最佳索引是什么？

我的问题是当我执行这个查询时需要很长时间才能完成

是查询或索引的复杂性吗？

蒂亚！

SELECT
 a.id
 a.name AS name
 CASE
   WHEN a.status IS NULL THEN '1111'
   WHEN a.status = '2222' THEN '3333'
   WHEN a.status = '4444' THEN '5555'
   ELSE a.status
 END AS status,
 a.updated_at
FROM a
LEFT JOIN b ON a.request_id = b.request_id
LEFT JOIN (
   SELECT 
    DISTINCT ON (id) id,
    name
   FROM
    aa
   WHERE
    updated_at BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59'
    AND id IN (
      SELECT id 
      FROM a
      WHERE 
      updated_at BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59'
      AND status NOT IN ('6666', '7777', '8888')
      AND id LIKE '%%'
                 )
)
WHERE 
a.updated_at BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59'
AND status NOT IN ('6666', '7777', '8888')
AND id LIKE '%%'

UNION

SELECT
 z.id
 z.name AS name
 CASE
   WHEN z.status IS NULL THEN '1111'
   WHEN z.status = '2222' THEN '3333'
   WHEN z.status = '4444' THEN '5555'
   ELSE z.status
 END AS status,
 z.updated_at
FROM z
LEFT JOIN zb ON z.request_id = zb.request_id
LEFT JOIN (
   SELECT 
    DISTINCT ON (id) id,
    name
   FROM
    zz
   WHERE
    updated_at BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59'
    AND id IN (
      SELECT id 
      FROM z
      WHERE 
      updated_at BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59'
      AND status NOT IN ('6666', '7777', '8888')
      AND id LIKE '%%'
                 )
)
WHERE 
z.updated_at BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59'
AND status NOT IN ('6666', '7777', '8888')
AND id LIKE '%%'

在使用表时删除或删除索引有什么影响？

样本：

从

表：order_tbl 索引：order_id_index（不是 btree）

至

需要将其更改为 index:order_id_index(btree)

删除索引时/之后是否有影响或数据丢失？