public inbox for [email protected]  

help / color / mirror / Atom feed

From: Moreno Andreo <[email protected]>
To: PostgreSQL mailing lists <[email protected]>
Subject: BTREE index: field ordering
Date: Fri, 28 Mar 2025 13:38:26 +0100
Message-ID: <[email protected]> (raw)

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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: BTREE index: field ordering
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox