Tuesday, September 16, 2008

Re: [GENERAL] Question about indexes

On Tue, Sep 16, 2008 at 12:24 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Sep 9, 2008 at 10:52 AM, regme please <regmeplease@gmail.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.

Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:

No comments: