Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSDQt-0001Wf-Lz for pgsql-general@arkaria.postgresql.org; Wed, 15 Feb 2023 08:46:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSDQr-00031Q-Es for pgsql-general@arkaria.postgresql.org; Wed, 15 Feb 2023 08:46:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSDQr-00031G-18 for pgsql-general@lists.postgresql.org; Wed, 15 Feb 2023 08:46:29 +0000 Received: from mail-wm1-x330.google.com ([2a00:1450:4864:20::330]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pSDQn-0001ne-LG for pgsql-general@postgresql.org; Wed, 15 Feb 2023 08:46:27 +0000 Received: by mail-wm1-x330.google.com with SMTP id l37-20020a05600c1d2500b003dfe46a9801so945986wms.0 for ; Wed, 15 Feb 2023 00:46:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=OzkPtS0XKXpBknCeDu9InTxG7LIkf6ImAv/NBFRpY1k=; b=3iBp1W7R+4HbPj14XO4GxF5z01XVo8iBN2f0IZZpy/c94WyTwFKIMhbVcWMyI+VtzG 8QCbdFu4KKE4A2d9aXNH1YJBuZqFu8zHN/SF1w+nVj9WJhEuvF+b3fP1saIbMcGT8VEW CXfykvF7AiBH7cLtHj/kQmn+dcJN0tsrjlgPf0ibUT1lEgvYjeaBBaa3t13ly+DIHAF9 K6zS3lgtwZIJQtDMQt+jWAW2+JAiyyt2QLO9Xq/k3mYwXJmZYfq2A1uXB50/LbMNv+9o SgyUfWXMr0bRvLsZkkHv7VhI2S2K6+4Otz72IIOtaYMkxfiWzASlV2ZYBP7U0uxZZ6Hy hzQQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=OzkPtS0XKXpBknCeDu9InTxG7LIkf6ImAv/NBFRpY1k=; b=p56SstdKXTeguAtpT7IktJ40d1zGQaH+EuiH9HIWS/tAGKtpI8TpQTx10fA/mEZ0wa o7xV3vQXnMuZCT4UyWV8IsnjReT7izTuT+YydbC945R6hqOfBDFQJjZs6yetESbDVvfg bN1n69gYctHVMEoIHlu0UNYLu5fq98XnpmU9zEiUTM9mBcar/VUpQOE5E3N4PEkYX5h/ OPHRORsOh1lOnXCBOypdIjsJy/F6+qedfaccp152ybcgt/uwubdtwHJ9dSAmrmKrDCH2 OJSKMw00V7wGVOfYHHCrGKGrwP+okeg70yu7hl6JHo0oCkbwTImjiWkPYm6u9+S9X8uo VMRg== X-Gm-Message-State: AO0yUKVSKQERmD83JDooad7ElT4/CSy7tUisNViPPM+vyTiL4/5O6i6X qOH4wSPlj6+k0ux/SW5RJOmkXFoqPtluB/Zw X-Google-Smtp-Source: AK7set+hsGXK7r8cRTFuc86oxmB/OJY1pY8ytQ874ZnGxk1UjpnGdKNKpfOBTgUeUCFu7d0uvXBtJA== X-Received: by 2002:a05:600c:997:b0:3df:e1cc:94ff with SMTP id w23-20020a05600c099700b003dfe1cc94ffmr1332906wmp.28.1676450784366; Wed, 15 Feb 2023 00:46:24 -0800 (PST) Received: from localhost.localdomain ([88.116.133.170]) by smtp.gmail.com with ESMTPSA id s9-20020a05600c45c900b003db06224953sm1423508wmo.41.2023.02.15.00.46.23 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 15 Feb 2023 00:46:24 -0800 (PST) Message-ID: <8780fa45a4041e4229f4a3fa88a61778d8534ab2.camel@cybertec.at> Subject: Re: Multi-column index: Which column order From: Laurenz Albe To: Sebastien Flaesch , "pgsql-general@postgresql.org" Date: Wed, 15 Feb 2023 09:46:23 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.3 (3.46.3-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 2023-02-14 at 17:53 +0000, Sebastien Flaesch wrote: > When creating an index on multiple columns, does the order of the columns= matter? > (I guess so) It does, but not in your case. > It's mostly for SELECT statements using a condition that include ALL colu= mns of the index (pkey): >=20 > =C2=A0=C2=A0 =C2=A0 SELECT * FROM art WHERE etb=3D'L1' and code=3D'ART345= ' >=20 > I would naturally put the columns with the most various values first, and >=20 > For example, if the "code" column contains thousands of various item ids = like 'SXZ874', > 'ERF345', ... while the "etb" column contains a dozen of values like "L1"= , "LT" and "BX". >=20 > Which one is best? >=20 > CREATE UNIQUE INDEX ix1 ON art (code, etb) > or > CREATE UNIQUE INDEX ix1 ON art (etb, code) >=20 > (or its PRIMARY KEY equivalent) Both are the same. There is an old myth that says that you should use the moew selective colum= n first (which would be "code"), but that is just a myth. Order makes a difference for queries like "WHERE etb =3D 'L1' and code LIKE= 'ART345'" or "WHERE code =3D 'ART345' ORDER BY etb", but not for two equality compari= sons. > Does it depend on the type of index (Btree, GiST, etc) ? Yes, it does. For B-tree and GiST indexes order matters, for hash, GIN and= BRIN indexes it doesn't (for various reasons). Yours, Laurenz Albe --=20 Cybertec | https://www.cybertec-postgresql.com