Content-Type: multipart/alternative;
 boundary="------------t387KcTYc1B9oYVJyesVQ3kT"
Message-ID: <a52070f6-9711-4ff5-a1bd-b3f5433fb843@evolu-s.it>
Date: Fri, 28 Mar 2025 13:38:26 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US, it
To: PostgreSQL mailing lists <pgsql-general@postgresql.org>
From: Moreno Andreo <moreno.andreo@evolu-s.it>
Subject: BTREE index: field ordering
Archived-At: <https://www.postgresql.org/message-id/a52070f6-9711-4ff5-a1bd-b3f5433fb843%40evolu-s.it>
Precedence: bulk

This is a multi-part message in MIME format.
--------------t387KcTYc1B9oYVJyesVQ3kT
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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
--------------t387KcTYc1B9oYVJyesVQ3kT
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-text-flowed"
      style="font-family: -moz-fixed; font-size: 12px;" lang="x-unicode">Hi,
      <br>
      Postgres 16.4 (planning to go on 17.4)
      <br>
      I'm creating some indexes based on some slow query reported by
      logs.
      <br>
      These queries involve a WHERE with more than 5 fields, that are
      matching by =, &lt;&gt;, LIKE and IN()
      <br>
      I read that equality fields must be first, then the others.
      <br>
      Is it correct?
      <br>
      <br>
      Based on this query
      <br>
      <br>
      SELECT COUNT(id) AS total
      <br>
                      FROM nx.tbl1
      <br>
                      WHERE
      <br>
                  (date_order &gt;= '2025-03-21')
      <br>
                  AND (date_order &lt;= '2025-03-29')
      <br>
                  AND (flag = TRUE)
      <br>
                  AND ((
      <br>
                    -- (flag = TRUE)
      <br>
                    -- AND (((tipo &lt;&gt; 'C') AND (tipo &lt;&gt; 'V')
      AND (tipo &lt;&gt; 'F')) OR (tipo IS NULL) OR (tipo = ''))
      <br>
                    (((op &lt;&gt; 'C') OR (op IS NULL)) OR (tipo =
      'F'))
      <br>
                    AND (s_state IN
      ('ENQ','WFR','BLQ','BLR','WFA','FRW','FRO','0000','0001'))
      <br>
                    AND (tiporic IS NOT NULL)
      <br>
                    AND (tiporic NOT LIKE '%cart%')
      <br>
                  ) OR (
      <br>
                    (tiporic LIKE '%cart%') AND (S_state &lt;&gt; 'CON')
      AND (s_state &lt;&gt; '0002') AND ((op &lt;&gt; 'C') OR (op IS
      NULL))
      <br>
                  ))  AND (priv IS NULL OR priv = false OR (priv = true
      and idpriv = 'TEST')));
      <br>
      <br>
      Should the following index be correct?
      <br>
      <br>
      CREATE INDEX IF NOT EXISTS tbl1_count_idx on nx.tbl1 USING
      BTREE(flag, tipo, op, priv, idpriv, date_order, s_state, tiporic);
      <br>
      <br>
      Would it be better to create a separate GIN/GIST index for the
      field matched with LIKE?
      <br>
      <br>
      Thanks in advance,
      <br>
      Moreno
      <br>
    </div>
  </body>
</html>

--------------t387KcTYc1B9oYVJyesVQ3kT--