Re: Question about indexes

On Tue, Sep 16, 2008 at 12:24 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Sep 9, 2008 at 10:52 AM, regme please <regmeplease(at)gmail(dot)com> wrote:
>> Hi all.
>> I usually create indexes accordingly to the queries used in my software.
>> This means the more often than not I have composited indexes over more than
>> one column.
>> What'd be in PGSQL (v8.3+) the pros and cons of having instead only
>> one-column indexes?
>> Thanks in advance.
>
> Pro:
> Fewer number of smaller well traveled indexes (more efficient from
> cache perspective).
>
> Con:
> Operations that look up multiple fields simultaneously run slower
> (sometimes much slower).
>
> Note that when reviewing sql written by other people in the
> performance list, probably the most common optimization suggestion is
> to use composite indexes.

The other, closely related optimization is functional indexes. If you
need to look up stuff based on date_trunc() then create indexes on
that. Next I'd say partial indexes.