BTREE index: field ordering

From: Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>
To: PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: BTREE index: field ordering
Date: 2025-03-28 12:38:26
Message-ID: a52070f6-9711-4ff5-a1bd-b3f5433fb843@evolu-s.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2025-03-28 13:35:05 Re: BTREE index: field ordering
Previous Message Laurenz Albe 2025-03-28 07:17:04 Re: COLLATION update in 13.1