public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Moreno Andreo <[email protected]>
To: PostgreSQL mailing lists <[email protected]>
Subject: Re: BTREE index: field ordering
Date: Fri, 28 Mar 2025 14:35:05 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

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], [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