Re: BTREE index: field ordering

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