Re: Multi-column indexes

On Sat, Jan 15, 2005 at 04:00:03PM -0500, Edmund Dengler wrote:
> Greetings!
>
> I have a technical question concerning multi-column indexes and their
> implementation. I tried looking for the answr in the docs but couldn't
> find anything.

<snip>
> I guess it breaks down to how these indexes are implemented. Are
> multi-column indexes implemented as true multiple level indexes, in the
> sense there is a level 1 index on <host_luid>, pointing to a level 2 index
> on <log_luid>, pointing to a level 3 index on <luid>? Or are they the
> equivalent of a <host_luid,log_luid,luid> single index (ie, as if I
> created a functional index consisting of
>         host_luid || ',' || log_luid || ',' || luid
> )?

It's a true multi-column index, so it'll go straight to the right
host_luid, then the log_luid and then start scanning. My guess is that
your problem is that there are lots of rows that match but have a
non-NULL error. Are you suffering from index bloat?

As a suggestion, if (error IS NULL) is something you search on
regularly and is a small portion of the table you should create a
partial index:

CREATE INDEX ... WHERE error IS NULL;

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.