On Sun, Oct 04, 2009 at 11:08:01AM -0400, Tom Lane wrote:
> Sam Mason writes:
> > On Sun, Oct 04, 2009 at 01:44:30AM -0700, tomrevam wrote:
> >> -> Bitmap Index Scan on session_allocation_info_status_idx
> >> (cost=0.00..5.28 rows=1 width=0) (actual time=1619.652..1619.652
> >> rows=51025 loops
tomrevam writes:
> Bill Moran wrote:
>> My apologies, I should have asked for the output of VACUUM VERBOSE on
>> the problem table in conjunction with these settings. (make sure you
>> do VACUUM VERBOSE when the table is exhibiting the speed problem)
> INFO: "session_allocation_info": found 388
Sam Mason writes:
> On Sun, Oct 04, 2009 at 01:44:30AM -0700, tomrevam wrote:
>> -> Bitmap Index Scan on session_allocation_info_status_idx (cost=0.00..5.28
>> rows=1 width=0) (actual time=1619.652..1619.652 rows=51025 loops=1)
>> Index Cond: ((status)::text = 'active'::text)
>> -> Bitmap Index
Bill Moran wrote:
>
> My apologies, I should have asked for the output of VACUUM VERBOSE on
> the problem table in conjunction with these settings. (make sure you
> do VACUUM VERBOSE when the table is exhibiting the speed problem)
>
INFO: vacuuming "public.session_allocation_info"
INFO: sc
On Sun, Oct 04, 2009 at 01:44:30AM -0700, tomrevam wrote:
> -> Bitmap Index Scan on session_allocation_info_status_idx
> (cost=0.00..5.28 rows=1 width=0) (actual time=1619.652..1619.652 rows=51025
> loops=1)
>Index Cond: ((status)::text = 'active'::text)
> -> B
Andy Colson-2 wrote:
>
> Can you post an explain analyze'es'es for (1) when its quick and (2)
> when its slow?
>
Here are results:
1. Upon startup:
QUERY
PLAN
In response to Vick Khera :
> On Tue, Sep 29, 2009 at 9:48 AM, Bill Moran wrote:
> > There has (over the last few years) been a lot of speculation from people
> > who think that indexes may suffer performance degradation under some
> > workloads. I've yet to see any actual evidence.
>
> Just la
In response to tomrevam :
>
My apologies, I should have asked for the output of VACUUM VERBOSE on
the problem table in conjunction with these settings. (make sure you
do VACUUM VERBOSE when the table is exhibiting the speed problem)
>
> Bill Moran wrote:
> >
> > The OP did mention that he's u
In response to tomrevam :
>
>
>
> Scott Marlowe-2 wrote:
> >
> > Just wondering, what version of pgsql are you running? I noticed a
> > lot less degradation from heavily updated tables when I went to 8.3
> > and set the fill % for tables / indexes to 90% or so. If you're
> > running an older
Scott Marlowe-2 wrote:
>
> Just wondering, what version of pgsql are you running? I noticed a
> lot less degradation from heavily updated tables when I went to 8.3
> and set the fill % for tables / indexes to 90% or so. If you're
> running an older version, the upgrade to 8.3 may well be wort
Bill Moran wrote:
>
> The OP did mention that he's using autovac, which will take care of
> both vacuum and analyze for him. However, he didn't provide his
> autovac config, and it happens at times that the defaults are not
> aggressive enough to keep a table well-maintained.
>
Here are my a
On Tue, Sep 29, 2009 at 5:23 AM, tomrevam wrote:
>
> I have a table with 5 million rows. 10 inserts and deletes are performed on
> this table every second.
> The table has indexes on the columns I use to query it, and the query is
> returning about a 1000 rows. Initially the query takes a very sho
On Tue, Sep 29, 2009 at 9:48 AM, Bill Moran wrote:
> There has (over the last few years) been a lot of speculation from people
> who think that indexes may suffer performance degradation under some
> workloads. I've yet to see any actual evidence.
>
Just last week I reindexed a 70+ million row t
Bill Moran writes:
>> My interpretation of the OPs problem was that the inserts and deletes
>> were happening at similar rates. Thus this won't be a problem.
> There has (over the last few years) been a lot of speculation from people
> who think that indexes may suffer performance degradation un
tomrevam wrote:
I have a table with 5 million rows. 10 inserts and deletes are performed on
this table every second.
The table has indexes on the columns I use to query it, and the query is
returning about a 1000 rows. Initially the query takes a very short time
(order of miliseconds), after a fe
In response to Sam Mason :
> On Tue, Sep 29, 2009 at 03:13:49PM +0200, Brian Modra wrote:
> > 2009/9/29 Sam Mason :
> > > Plain vacuum should allow things to reach a steady state after
> > > a while,
> >
> > If there are a lot of deletes, then likely the index parameters are
> > not the best.
>
On Tue, Sep 29, 2009 at 03:13:49PM +0200, Brian Modra wrote:
> 2009/9/29 Sam Mason :
> > Plain vacuum should allow things to reach a steady state after
> > a while,
>
> If there are a lot of deletes, then likely the index parameters are
> not the best.
My interpretation of the OPs problem was tha
2009/9/29 Sam Mason :
> On Tue, Sep 29, 2009 at 02:25:52PM +0200, Brian Modra wrote:
>> 2009/9/29 tomrevam :
>> > My DB is auto-vacuuming all the time. The specific table I'm talking about
>> > gets vacuumed at least every 2 hours (usually a little more frequently than
>> > that).
>> > Deletes are
On Tue, Sep 29, 2009 at 02:25:52PM +0200, Brian Modra wrote:
> 2009/9/29 tomrevam :
> > My DB is auto-vacuuming all the time. The specific table I'm talking about
> > gets vacuumed at least every 2 hours (usually a little more frequently than
> > that).
> > Deletes are happening on the table at abo
2009/9/29 tomrevam :
>
>
>
> Brian Modra-2 wrote:
>>
>> When did you last do an analyse and re-create indexes?
>> Is the table UPDATEd to or DELETEd from, or just INSERTed ?
>> Is your DB auto vacuuming?
>>
>
> My DB is auto-vacuuming all the time. The specific table I'm talking about
> gets vacuum
Brian Modra-2 wrote:
>
> When did you last do an analyse and re-create indexes?
> Is the table UPDATEd to or DELETEd from, or just INSERTed ?
> Is your DB auto vacuuming?
>
My DB is auto-vacuuming all the time. The specific table I'm talking about
gets vacuumed at least every 2 hours (usually
2009/9/29 tomrevam :
>
> I have a table with 5 million rows. 10 inserts and deletes are performed on
> this table every second.
> The table has indexes on the columns I use to query it, and the query is
> returning about a 1000 rows. Initially the query takes a very short time
> (order of milisecon
I have a table with 5 million rows. 10 inserts and deletes are performed on
this table every second.
The table has indexes on the columns I use to query it, and the query is
returning about a 1000 rows. Initially the query takes a very short time
(order of miliseconds), after a few hours it takes
23 matches
Mail list logo
