Hi,
Postgres 16.4 (planning to go on 17.4)
I'm creating some indexes based on some slow query reported by logs.
These queries involve a WHERE with more than 5 fields, that are matching by =, <>, LIKE and IN()
I read that equality fields must be first, then the others.
Is it correct?
Based on this query
SELECT COUNT(id) AS total
FROM nx.tbl1
WHERE
(date_order >= '2025-03-21')
AND (date_order <= '2025-03-29')
AND (flag = TRUE)
AND ((
-- (flag = TRUE)
-- AND (((tipo <> 'C') AND (tipo <> 'V') AND (tipo <> 'F')) OR (tipo IS NULL) OR (tipo = ''))
(((op <> 'C') OR (op IS NULL)) OR (tipo = 'F'))
AND (s_state IN ('ENQ','WFR','BLQ','BLR','WFA','FRW','FRO','0000','0001'))
AND (tiporic IS NOT NULL)
AND (tiporic NOT LIKE '%cart%')
) OR (
(tiporic LIKE '%cart%') AND (S_state <> 'CON') AND (s_state <> '0002') AND ((op <> 'C') OR (op IS NULL))
)) AND (priv IS NULL OR priv = false OR (priv = true and idpriv = 'TEST')));
Should the following index be correct?
CREATE INDEX IF NOT EXISTS tbl1_count_idx on nx.tbl1 USING BTREE(flag, tipo, op, priv, idpriv, date_order, s_state, tiporic);
Would it be better to create a separate GIN/GIST index for the field matched with LIKE?
Thanks in advance,
Moreno
