Re: Table Design question for gurus (without going to "NoSQL")...

On 21 Listopad 2011, 4:17, David Johnston wrote:
> On Nov 20, 2011, at 20:50, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
>
>> On Mon, Nov 21, 2011 at 7:26 AM, Gavin Flower
>> <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
>>
>>> How about having 2 indexes: one on each of ip & url_md5? Pg will
>>> combine the
>>> indexes as required, or will just use one if that is best.
>>
>>
>>
>> Thanks Gavin. Question: what if I have a joined index? If from a
>> joined index I only use the first column (say, "ip") will a joined
>> index still be used?
>>
>> It is cleaner to create two indexes for the two columns. Which is
>> recommended?
>>
>
> An index on (a, b) can be used for queries involving only a but not for
> those involving only b.

That is not true since 8.2 - a multi-column index may be used even for
queries without conditions on leading columns. It won't be as effective as
with conditions on leading columns, because the whole index must be
scanned, but it's usually much cheaper than keeping two indexes (memory
requirements, overhead when inserting data etc.)

Check this:
http://www.postgresql.org/docs/8.2/interactive/indexes-multicolumn.html

Tomas