Re: Question about indexes

From: Reg Me Please <regmeplease(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Merlin Moncure" <mmoncure(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Question about indexes
Date: 2008-09-16 19:49:47
Message-ID: 200809162149.47374.regmeplease@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Reid.Thompson 2008-09-16 20:46:37 Is there a parameter to tell postgresql to not attempt to open an IPv6 socket?
Previous Message Brent Wood 2008-09-16 19:10:17 Re: foreign key problem