Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37604=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1VyoW7-0005m8-NJ
 for pgsql-sql@arkaria.postgresql.org; Thu, 02 Jan 2014 20:05:20 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37604=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1VyoW7-0003sf-49
 for pgsql-sql@arkaria.postgresql.org; Thu, 02 Jan 2014 20:05:19 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jonathan.katz@excoventures.com>) id 1VyoW6-0003sQ-A7
 for pgsql-sql@postgresql.org; Thu, 02 Jan 2014 20:05:18 +0000
Received: from mail-qa0-f54.google.com ([209.85.216.54])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jonathan.katz@excoventures.com>) id 1VyoW3-0004zK-Dj
 for pgsql-sql@postgresql.org; Thu, 02 Jan 2014 20:05:17 +0000
Received: by mail-qa0-f54.google.com with SMTP id f11so13546345qae.20
 for <pgsql-sql@postgresql.org>; Thu, 02 Jan 2014 12:05:14 -0800 (PST)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:subject:mime-version:content-type:from
 :in-reply-to:date:cc:content-transfer-encoding:message-id:references
 :to; bh=GUoI9UEeuU6gciJIbPRNDEyoltE/we5G/vdxvXd3B/s=;
 b=iP1uW+dkByOJusosCB+J2dzA/DpubDnT67jwfZ46bOWdtJnfKdQF0uKvsP44A4JXQ8
 gIE8clhDaHxH6rcWHbiPHEWjjsWCDS/Xcr151zydd3v/J1dap3GOT0tWJM19uSuzxwke
 Uc96crcgjPTWMAzynTpdKw32ZWhUkOAmD7YIjQZiyciUqPP6LyEioWEb55lakop4Fo+P
 Ga3RAK6QPQJnDgG3Thj8sY6JBGBhL8jrM2D/TwJic+oLSUhixmxcHd2NGB3MkOsHEXx5
 ffHmix6kU9O9gvQayU0UbjNw8Ht6Q10JdStlyfA8CmFHviI9KM8kCnDsPNQvx/BdNzPW
 7sVg==
X-Gm-Message-State: 
 ALoCoQmaoTsryeaDm2crHwrVLd3s+gMscFCuzal3SIvH0aSdFJ3vydUXBWy6RHs1afxJ21zwrFBZ
X-Received: by 10.49.52.102 with SMTP id s6mr144256988qeo.60.1388693114547;
 Thu, 02 Jan 2014 12:05:14 -0800 (PST)
Received: from [10.0.0.28] (pool-108-6-136-93.nycmny.fios.verizon.net.
 [108.6.136.93])
 by mx.google.com with ESMTPSA id j9sm75690338qeo.18.2014.01.02.12.05.13
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 02 Jan 2014 12:05:13 -0800 (PST)
Subject: Re: Index on multiple columns VS multiple index
Mime-Version: 1.0 (Apple Message framework v1283)
Content-Type: text/plain; charset=iso-8859-1
From: "Jonathan S. Katz" <jonathan.katz@excoventures.com>
In-Reply-To: 
 <CAOBG12krR4LOsompeUKBe2qVT5zHk-9+3K16H=AVTPGMt8yfNw@mail.gmail.com>
Date: Thu, 2 Jan 2014 15:05:11 -0500
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <F430EDB0-191D-4CB9-9C2A-C967AFCF8772@excoventures.com>
References: 
 <CAOBG12m10zcGwHY_g8+Mwhs2sXjBvue5EqidHGj+pZtH3XFCXw@mail.gmail.com>
 <260BFF81-D9DB-4A71-9D17-2BD7C1F4C343@excoventures.com>
 <CAOBG12nmg+fheEgoqk=B4zHd-6tqfwAkFt1H6G=cz6e5bXr_QQ@mail.gmail.com>
 <97DDB603-D01D-4355-872A-5913DBBC5268@excoventures.com>
 <CAOBG12krR4LOsompeUKBe2qVT5zHk-9+3K16H=AVTPGMt8yfNw@mail.gmail.com>
To: =?iso-8859-1?Q?Brice_Andr=E9?= <brice@famille-andre.be>
X-Mailer: Apple Mail (2.1283)
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Hi Brice,

On Jan 2, 2014, at 2:45 PM, Brice Andr=E9 wrote:

> Sorry, it's probably my bad english that is confusing. When I sayed inequ=
ality, I ment operators like <=3D or >=3D. In fact, 'a' is an integer' whic=
h is a foreign key on a primary key of another table and b is a timestamp.
>=20
> So, if I understand you, making an index on both ('a', 'b') will be faste=
r than two separate indices ?
>=20
> But, if yes, does this index can be useful for a search on 'a' only ? Or =
do I need a separate index for this ?
>=20
> I generally also have a ORDER clause on 'b'. I suppose that the index wil=
l be a good point for it too ?

It should be more space-efficient to use a multi-column index, but you woul=
d have similar performance on SELECTs as having two indexes.

But now understanding your data a bit more, it probably would be better jus=
t to have an index on "a" - unless you will be searching only over "b" or e=
ven after filtering your data by "a" you will have a lot of rows that need =
to be filtered by "b" having a multi-column index or an index on "b" would =
be probably be overkill.=20

Of course, I use "a lot" because it really depends on what your actual data=
 is - if you can you should probably run a few scenarios with your data set=
 (if you can) and using EXPLAIN ANALYZE to see which index or indexes actua=
lly are used.

Keep in mind that you have to consider what happens when you have writes (I=
NSERT/UPDATE/DELETE) on the table with your indexes, your write queries wil=
l have to wait for those indexes to be updated, thus putting more I/O load =
on the system.

> And, last question, I also have time-consuming queries that are of the fo=
rm :=20
>=20
> SELECT .. FROM table WHERE 'a'=3Dx AND 'c'=3Dy AND 'b' >=3D z
>=20
> where 'c' is an integer, but that is not a foreign key. Does it makes sen=
se to create an additional multi-column index on ('a', 'b', 'c') ? Does the=
 order of declaration of columns in the index creation makes a difference ?=
 (for example ('a', 'c', 'b')) ? And is this index useful for a search on '=
a' and 'b' only ?

Probably not, unless you have a lot of rows to further filter from "c" - to=
o much indexing could actually impede performance, which is why you need to=
 experiment a bit :-)

Best,

Jonathan

--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql