pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jonathan S. Katz <jonathan.katz@excoventures.com>
To: Brice André <brice@famille-andre.be>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Index on multiple columns VS multiple index
Date: Thu, 2 Jan 2014 15:05:11 -0500
Message-ID: <F430EDB0-191D-4CB9-9C2A-C967AFCF8772@excoventures.com> (raw)
In-Reply-To: <CAOBG12krR4LOsompeUKBe2qVT5zHk-9+3K16H=AVTPGMt8yfNw@mail.gmail.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

Hi Brice,

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

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

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

But now understanding your data a bit more, it probably would be better just to have an index on "a" - unless you will be searching only over "b" or even 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. 

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 actually are used.

Keep in mind that you have to consider what happens when you have writes (INSERT/UPDATE/DELETE) on the table with your indexes, your write queries will 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 form : 
> 
> SELECT .. FROM table WHERE 'a'=x AND 'c'=y AND 'b' >= z
> 
> where 'c' is an integer, but that is not a foreign key. Does it makes sense 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" - too much indexing could actually impede performance, which is why you need to experiment a bit :-)

Best,

Jonathan

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



view thread (9+ messages)  latest in thread

Message-ID: <F430EDB0-191D-4CB9-9C2A-C967AFCF8772@excoventures.com>
Permalink:  ../F430EDB0-191D-4CB9-9C2A-C967AFCF8772@excoventures.com/
Also on:    postgresql.org/message-id/F430EDB0-191D-4CB9-9C2A-C967AFCF8772@excoventures.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jonathan.katz@excoventures.com, brice@famille-andre.be
  Subject: Re: Index on multiple columns VS multiple index
  In-Reply-To: <F430EDB0-191D-4CB9-9C2A-C967AFCF8772@excoventures.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox