public inbox for [email protected]
help / color / mirror / Atom feedBTREE index: field ordering
3+ messages / 3 participants
[nested] [flat]
* BTREE index: field ordering
@ 2025-03-28 12:38 Moreno Andreo <[email protected]>
2025-03-28 13:35 ` Re: BTREE index: field ordering Laurenz Albe <[email protected]>
2025-03-29 07:28 ` Re: BTREE index: field ordering Kevin Stephenson <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Moreno Andreo @ 2025-03-28 12:38 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BTREE index: field ordering
2025-03-28 12:38 BTREE index: field ordering Moreno Andreo <[email protected]>
@ 2025-03-28 13:35 ` Laurenz Albe <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2025-03-28 13:35 UTC (permalink / raw)
To: Moreno Andreo <[email protected]>; pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BTREE index: field ordering
2025-03-28 12:38 BTREE index: field ordering Moreno Andreo <[email protected]>
@ 2025-03-29 07:28 ` Kevin Stephenson <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Kevin Stephenson @ 2025-03-29 07:28 UTC (permalink / raw)
To: Moreno Andreo <[email protected]>; pgsql-general
Moreno,
You have two lines in your WHERE clause commented out, the first line is a duplicate check on flag = TRUE, and the other line involves several checks on the "tipo" column. Will the final query or set of related queries actually need to filter on the "tipo" column? You currently have "tipo" in second place in your candidate index.
Thanks,
Kevin
________________________________
From: Moreno Andreo <[email protected]>
Sent: Friday, March 28, 2025 5:38 AM
To: PostgreSQL mailing lists <[email protected]>
Subject: BTREE index: field ordering
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-29 07:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-28 12:38 BTREE index: field ordering Moreno Andreo <[email protected]>
2025-03-28 13:35 ` Laurenz Albe <[email protected]>
2025-03-29 07:28 ` Kevin Stephenson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox