On Fri, 2025-03-28 at 13:38 +0100, Moreno Andreo wrote:
> 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?
Fundamentally yes, but you also have to consider how selective the conditions are.
Putting a column in the index where the condition will only filter out few rows
is not going to help; such rows should be omitted from the index.
> 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?
The ORs will be a problem. Get rid of them as much as possible by using UNION,
at least for WHERE conditions that are selective.
Yours,
Laurenz Albe
