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

On 21/11/11 14:50, Phoenix Kiula 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 will use the joined index if the planner thinks it is worth it.
However, it is harder for the planner to jusify using the index for the
second field when the query does not restrict on the first field (I am
assuming it can, if required).

>
> It is cleaner to create two indexes for the two columns. Which is recommended?
If you are frequently just using one or other of the indexes and/or
could effectively use a joined index in both directins - then szeparate
indexes will probably be better.

If your predominant query mode can use just use the one joined index
effectively, then that would be better.

Consider the amount of RAM the indexes and table data will take up.

The advantages of indexing 2 fields separately compared to one =joined
index are: that if you only need either single field index, it will take
up less RAM and be also be quicker to read from disk. Plus the 2 single
field indexes can be used together for queiries that use both fields.
The costs are that when both indexes need to be used, there is a little
bit more processing involved, and 2 single field indexes take up more
RAM than a single joined index.

So the answer is 'it depends...'!