Thread: Question about indexes

Question about indexes

From
"regme please"
Date:
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.

Re: Question about indexes

From
Reg Me Please
Date:
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.



Re: Question about indexes

From
"Merlin Moncure"
Date:
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

Re: Question about indexes

From
"Scott Marlowe"
Date:
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.

Re: Question about indexes

From
Reg Me Please
Date:
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.