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
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
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: scanned
Sam Mason s...@samason.me.uk 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)
-
tomrevam to...@fabrix.tv 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:
On Sun, Oct 04, 2009 at 11:08:01AM -0400, Tom Lane wrote:
Sam Mason s...@samason.me.uk 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
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
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 worth
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 version, the
In response to tomrevam to...@fabrix.tv:
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
In response to Vick Khera vi...@khera.org:
On Tue, Sep 29, 2009 at 9:48 AM, Bill Moran wmo...@potentialtech.com 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
2009/9/29 tomrevam to...@fabrix.tv:
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
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 a
2009/9/29 tomrevam to...@fabrix.tv:
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
On Tue, Sep 29, 2009 at 02:25:52PM +0200, Brian Modra wrote:
2009/9/29 tomrevam to...@fabrix.tv:
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
2009/9/29 Sam Mason s...@samason.me.uk:
On Tue, Sep 29, 2009 at 02:25:52PM +0200, Brian Modra wrote:
2009/9/29 tomrevam to...@fabrix.tv:
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
On Tue, Sep 29, 2009 at 03:13:49PM +0200, Brian Modra wrote:
2009/9/29 Sam Mason s...@samason.me.uk:
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
In response to Sam Mason s...@samason.me.uk:
On Tue, Sep 29, 2009 at 03:13:49PM +0200, Brian Modra wrote:
2009/9/29 Sam Mason s...@samason.me.uk:
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
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
Bill Moran wmo...@potentialtech.com 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
On Tue, Sep 29, 2009 at 9:48 AM, Bill Moran wmo...@potentialtech.com 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
On Tue, Sep 29, 2009 at 5:23 AM, tomrevam to...@fabrix.tv 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
22 matches
Mail list logo