Skip site navigation (1) Skip section navigation (2)

Re: Index performance

From: "Brian Modra" <epailty(at)googlemail(dot)com>
To: "Andrew Sullivan" <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Index performance
Date: 2008-01-03 05:11:07
Message-ID: 5a9699850801022111j44d91955h214cb559e47d8cb1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Thanks, I think you have me on the right track. I'm testing a vacuum
analyse now to see how long it takes, and then I'll set it up to
automatically run every night (so that it has a chance to complete
before about 6am.)

On 02/01/2008, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
> On Wed, Jan 02, 2008 at 05:53:35PM +0200, Brian Modra wrote:
> > This table is added to in real time, at least 10 rows per second.
>
> [. . .]
>
> > If I do a select which uses the pkey index, where equal to the ID
> > column, and greater than one of the values, which should return about
> > 1500 rows, it sometimes takes 1/2 minute to return, and other times
> > takes only seconds.
> >
> > Is it the number of rows being added in real time, that is maybe
> > causing the index to be locked?
>
> No, it's probably a bad plan.  A minimum 10 rows/second is probably just
> making the statistics for the table look bad.  You likely want to SET
> STATISTICS wider on the 1st (~150 distinct values) column, and then run
> ANALYSE on the table very frequently.  Are you updating or deleting at all?
> If so, that will also affect things: you need to perform very frequent
> VACUUM on that table in that case.
>
> Aside from that generic advice, it's impossible to say more without EXPLAIN
> ANALYSE output for the slow and fast examples.
>
> A
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>


-- 
Brian Modra   Land line: +27 23 5411 462
Mobile: +27 79 183 8059
6 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa

In response to

Responses

pgsql-hackers by date

Next:From: Gokulakannan SomasundaramDate: 2008-01-03 07:04:00
Subject: Re: Table rewrites vs. pending AFTER triggers
Previous:From: Sam MasonDate: 2008-01-03 00:41:04
Subject: Re: Dynamic Partitioning using Segment Visibility Maps

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group