Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tyCVE-003kEE-2J for pgsql-general@arkaria.postgresql.org; Fri, 28 Mar 2025 16:24:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tyCVC-00CawU-F3 for pgsql-general@arkaria.postgresql.org; Fri, 28 Mar 2025 16:24:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tyCVC-00CauB-0H for pgsql-general@lists.postgresql.org; Fri, 28 Mar 2025 16:24:14 +0000 Received: from mail-oo1-xc34.google.com ([2607:f8b0:4864:20::c34]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tyCVA-001eS7-0L for pgsql-general@postgresql.org; Fri, 28 Mar 2025 16:24:13 +0000 Received: by mail-oo1-xc34.google.com with SMTP id 006d021491bc7-601ebdf02daso1332370eaf.0 for ; Fri, 28 Mar 2025 09:24:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1743179051; x=1743783851; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=rD0pp6PBCo6G9icbih2J7VD6qc77a4DdwOcPwTNcmEI=; b=WN7uXvmimpJ0oyTexDSb1D5TxlNF+KumE///98G0eIj6cymRrEQAR7VzriJTW3Uyt8 R6GCXw4VCf/7NgnRKwhOvGgVOmgZC+qki8KrPkVFFyg7d1cBfxT4/LaaumoVXOrjyufD XSXjrLHdbwUP9Z6KN6eDTnAWt9YpVw6+Y3lVebOyT2syZ9MKVxWLnxMI4DIf3EG4c0WB r7hJMqkELt+aBtYgUCTg2Kp0fuuJukHqDvXFGR4Agz5F3Ubln2e6W+HNDYYS3ZCDiOJp xhW7mwMLjLJkniG0r512exocQksLWHIkY9xhagXzlMXLJ8sa+MnzbvSAV7EfH3jWdZ7i 5aUQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1743179051; x=1743783851; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=rD0pp6PBCo6G9icbih2J7VD6qc77a4DdwOcPwTNcmEI=; b=nkgYqSRR7zPVbZr098O7Z0zG4OHMk1afjQzalKtlguutXZErc+LNR7jsX5630HCArf eALGLu++Q+Uljppz4lOdPyuDy8sVa7Pwwb+iFlbe3Wfj+YauQ2XnMqu8DJXE7Mu3Ibnz 9s7zpIWaMFc8yBlT2nNpCn77R11wNaex8XW1Yi6tkQTsjSLyXbGsijHpMkPQj4fJweNU mbCAb1/sfRizAE9NnDWf5yNr7D3cOjN98D2sCMI1iwDGAVMmxhWyjICBNH7X9FuyqOln iI155NGfec8bLnsuRPILJkKyXBBXmjKwjyngBEAYEC5F+onhek6efDXweiX4BvVNqR2F XqIA== X-Gm-Message-State: AOJu0YwHawyvtGNT6s5ZgwKWqJJdzrShpFkeOBQ6lW28qhNcyKwQSxmj UKXNFSbUTqZrdvuRS9Fu4HmBCJcvvFFJtvECWAOTTP4Mo/2bIIZJrnUC3DFF7sTkj1YCSpKC1xR lLpPsfadDj9m1zHju6+9FzK3iUBK+LtNH X-Gm-Gg: ASbGncvZUI011r/RSmBvmBPIc442fCNfTiujriBKha0IOmq7Z+Ybd00aC0KdlFA7vu5 rLYHDG0vYGJHOajOFAWXgM+JkGTR7tIfBKIwIswizzAGB7G5NXSsG5PxlzoX82XG9gORvqkJR9a F7mAPAl3G8FAZaT2GFHSo4Fx+lKcmuTMOmWVg8vjSs/rOP6ALe3hJKyDO1rKo= X-Google-Smtp-Source: AGHT+IH9v6yeiq29x6yeuiqA4cBaPhmFdYVQ0iMhJg29/w9FXbDHh9D1dpJ9VggKKsKIGh6JOwM7Gs4tNMk5b2zJeZY= X-Received: by 2002:a05:6820:178e:b0:600:5673:69ef with SMTP id 006d021491bc7-6029023d62dmr46007eaf.1.1743179051029; Fri, 28 Mar 2025 09:24:11 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Fri, 28 Mar 2025 12:24:00 -0400 X-Gm-Features: AQ5f1JrKBxhX50UPhtGc6Eo-AJY_X3cyp2_i2y0KWbPyqlciWjxsSOTU7al1wPY Message-ID: Subject: Re: BTREE index: field ordering To: PostgreSQL mailing lists Content-Type: multipart/alternative; boundary="0000000000001de3a206316980c1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001de3a206316980c1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Mar 28, 2025 at 9:35=E2=80=AFAM Laurenz Albe wrote: > 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 =3D, <>, 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 fe= w > 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 >=3D '2025-03-21') > > AND (date_order <=3D '2025-03-29') > > AND (flag =3D TRUE) > > AND (( > > -- (flag =3D TRUE) > > -- AND (((tipo <> 'C') AND (tipo <> 'V') AND (tipo <> > 'F')) OR (tipo IS NULL) OR (tipo =3D '')) > > (((op <> 'C') OR (op IS NULL)) OR (tipo =3D '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 =3D false OR (priv =3D true = and > idpriv =3D '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. > "at least for WHERE conditions that are selective" confuses me. Aren't _all_ WHERE clauses selective? --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000001de3a206316980c1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Mar 28, 2025 at 9:35=E2=80=AFAM L= aurenz Albe <laurenz.albe@cy= bertec.at> wrote:
On Fri, 2025-03-28 at= 13:38 +0100, Moreno Andreo wrote:
> =C2=A0Postgres 16.4 (planning to go on 17.4)
> =C2=A0I'm creating some indexes based on some slow query reported = by logs.
> =C2=A0These queries involve a WHERE with more than 5 fields, that are = matching by =3D, <>, LIKE and IN()
> =C2=A0I read that equality fields must be first, then the others.
> =C2=A0Is it correct?

Fundamentally yes, but you also have to consider how selective the conditio= ns 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
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0SEL= ECT COUNT(id) AS total=C2=A0
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM nx.tbl1
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 WHERE
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 (date_order >=3D '2025-03-21')
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 AND (date_order <=3D '2025-03-29')
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 AND (flag =3D TRUE)
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 AND ((
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 -- (flag =3D TRUE)
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 -- AND (((tipo <> 'C') AND (tipo <> = 9;V') AND (tipo <> 'F')) OR (tipo IS NULL) OR (tipo =3D &= #39;'))
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 (((op <> 'C') OR (op IS NULL)) OR (tipo =3D &= #39;F'))
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 AND (s_state IN ('ENQ','WFR','BLQ',= 'BLR','WFA','FRW','FRO','0000','= ;0001'))
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 AND (tiporic IS NOT NULL)
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 AND (tiporic NOT LIKE '%cart%')
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 ) OR (
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 (tiporic LIKE '%cart%') AND (S_state <> '= CON') AND (s_state <> '0002') AND ((op <> 'C= 9;) OR (op IS NULL))
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 ))=C2=A0 AND (priv IS NULL OR priv =3D false OR (priv =3D true and idpr= iv =3D 'TEST')));
>
> =C2=A0Should the following index be correct?
>
> =C2=A0CREATE INDEX IF NOT EXISTS tbl1_count_idx on nx.tbl1 USING BTREE= (flag, tipo, op, priv, idpriv, date_order, s_state, tiporic);
> =C2=A0
> =C2=A0Would it be better to create a separate GIN/GIST index for the f= ield matched with LIKE?

The ORs will be a problem. Get rid of them as much as possible by using UNI= ON,
at least for WHERE conditions that are selective.
=C2= =A0
"at least for WHERE conditions that are selective&= quot; confuses me.=C2=A0 Aren't _all_ WHERE clauses selective?

--
Death to <Redacted&g= t;, and butter sauce.
Don't boil me, I'm still alive.
<= div><Redacted> lobster!
--0000000000001de3a206316980c1--