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