Thread: Question about indexes

Any hint?

> 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.
>
> RMP.

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.

merlin

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.

As I told you, I use to design indexes based upon the queries, the WHERE
clauses especially.

My fear is that in PGSQL the runtime "index composition" can be a drawback to
the performances if compared to "static index composition".

Is this true accordingly to your experience?
Is there any "best common practice" for this issue in PGSQL?

Thanks again.

On Tuesday 16 September 2008 20:41:22 Scott Marlowe wrote:
> 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.