Interesting - I haven't seen that tool before. I'll have to check it
out when I get a chance. Thanks!
On Wed, 17 Jan 2007 20:32:37 +0100, "Tomas Vondra" <[EMAIL PROTECTED]> said:
> > That's about 32% dead rows. Might be worth scheduling a vacuum full,
> > but it's not like I was afraid it migh
> That's about 32% dead rows. Might be worth scheduling a vacuum full,
> but it's not like I was afraid it might be. It looks to me like you
> could probably use a faster I/O subsystem in that machine though.
>
> If the random page cost being lower fixes your issues, then I'd just run
> with it
>> Assuming the table's NOT bloated, you may do well to increase the
>> effective_cache_size, which doesn't allocate anything,
>
>> try setting it to something like 512MB or so.
>
> It's currently set to 1000MB.
>
>
>> If your table is bloating, and you don't have idle transactions hanging
>>
> Also, look at the thread going by about index bloat by 4x. You'll
> likely want to reindex after a vacuum full since vacuum full doesn't
> reclaim space in indexes and in fact often bloats indexes.
Thanks for the pointer. That thread might indeed apply to my situation.
I'm going to reindex th
> It would be nice if the database could
> learn to estimate these values, as newer versions of Oracle does.
That would be really nice since it would take some of the guess work out
of it.
> Yes, cluster would rebuild the table for you. I wouldn't do anything too
> intrusive, run with the random
On Wed, 2007-01-17 at 10:28, Jeremy Haile wrote:
> > That's about 32% dead rows. Might be worth scheduling a vacuum full,
> > but it's not like I was afraid it might be. It looks to me like you
> > could probably use a faster I/O subsystem in that machine though.
>
> I'll try to schedule a full
On 1/17/07, Jeremy Haile <[EMAIL PROTECTED]> wrote:
Maybe - I tried running the same query for an older time range that is
less likely to be cached. The index scan took longer than my previous
example, but still only took 16 seconds, compared to the 87 seconds
required to seqscan the table. Wh
> That's about 32% dead rows. Might be worth scheduling a vacuum full,
> but it's not like I was afraid it might be. It looks to me like you
> could probably use a faster I/O subsystem in that machine though.
I'll try to schedule a full vacuum tonight. As far as I/O - it's using
SAN over fiber.
On Wed, 2007-01-17 at 08:37, Jeremy Haile wrote:
> > I still keep wondering if this table is bloated with dead tuples. Even
> > if you vacuum often if there's a connection with an idle transaction,
> > the tuples can't be reclaimed and the table would continue to grow.
>
> I used to vacuum once a
> How much memory does the box have
2GB
> Yes, it takes up space
Well, I upped max_fsm_pages to 200 because it vacuums were failing
with it set to 150. However, I'm now autovacuuming, which might be
keeping my fsm lower. I didn't realize that setting it too high had
negative effects, so
On 17-Jan-07, at 9:37 AM, Jeremy Haile wrote:
I still keep wondering if this table is bloated with dead tuples.
Even
if you vacuum often if there's a connection with an idle transaction,
the tuples can't be reclaimed and the table would continue to grow.
I used to vacuum once an hour, alth
Thanks for the great info Chad. I'm learning a lot from this thread!
> 347434 rows * 156 bytes = 52MB (reasonable it could be held in your
> shared buffers, which makes Tom's suggestion very plausible, the
> index scan may not be cheaper -- because it is all cached)
Maybe - I tried running the
A good idea here will be to first do a VACUUM FULL and then keep the
Autovacuum settings you want.
-
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 1/17/07, Jeremy Haile <[EMAIL PROTECTED]> wrote:
> I still keep wondering if this table is bloated with dead tuples. Even
> if
> I still keep wondering if this table is bloated with dead tuples. Even
> if you vacuum often if there's a connection with an idle transaction,
> the tuples can't be reclaimed and the table would continue to grow.
I used to vacuum once an hour, although I've switched it to autovacuum
now. It de
On Tue, 2007-01-16 at 21:58 -0500, Jeremy Haile wrote:
> Hey Chad,
>
> The table is heavily inserted and deleted from. Recently I had done a
> very large delete.
I still keep wondering if this table is bloated with dead tuples. Even
if you vacuum often if there's a connection with an idle trans
On 1/16/07, Jeremy Haile <[EMAIL PROTECTED]> wrote:
The table is heavily inserted and deleted from. Recently I had done a
very large delete.
That's what I suspected.
Here is the results of the query you sent me: (sorry it's hard to read)
"transaction_date";0;8;172593;-0.194848
Just curi
Hey Chad,
The table is heavily inserted and deleted from. Recently I had done a
very large delete.
Here is the results of the query you sent me: (sorry it's hard to read)
"dcms_dim_id";0;4;755;-0.00676181
"transaction_fact_id";0;4;-1;-0.194694
"failed";0;4;2;0.964946
"van16";0;23;145866;0.00978
On 1/16/07, Jeremy Haile <[EMAIL PROTECTED]> wrote:
Even if unrelated, do you think disk fragmentation would have negative
effects? Is it worth trying to defragment the drive on a regular basis
in Windows?
Out of curiosity, is this table heavily updated or deleted from? Perhaps
there is an
Thanks Tom! Reducing random_page_cost to 2 did the trick for this
query. It now favors the index scan.
Even if this is a cached situation, I wouldn't expect a difference of 3
min vs 3 seconds.
Even if unrelated, do you think disk fragmentation would have negative
effects? Is it worth trying
"Jeremy Haile" <[EMAIL PROTECTED]> writes:
> Running PostgreSQL 8.2.1 on Win32. The query planner is choosing a seq
> scan over index scan even though index scan is faster (as shown by
> disabling seqscan). Table is recently analyzed and row count estimates
> seem to be in the ballpark.
Try red
Running PostgreSQL 8.2.1 on Win32. The query planner is choosing a seq
scan over index scan even though index scan is faster (as shown by
disabling seqscan). Table is recently analyzed and row count estimates
seem to be in the ballpark.
Another tidbit - I haven't done a "vacuum full" ever, alth
21 matches
Mail list logo
