Re: BTREE index: field ordering

On Fri, Mar 28, 2025 at 9:35 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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.
>

"at least for WHERE conditions that are selective" confuses me. Aren't
_all_ WHERE clauses selective?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!