Re: [NOVICE] Extreme high load averages - Mailing list pgsql-performance

From Martin Foster
Subject Re: [NOVICE] Extreme high load averages
Date
Msg-id 3F0E54A2.4040604@ethereal-realms.org
Whole thread Raw
In response to Re: [NOVICE] Extreme high load averages  ("Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>)
List pgsql-performance
Shridhar Daithankar wrote:

> On 10 Jul 2003 at 0:43, Martin Foster wrote:
>
>>As for creating a new table, that in itself is a nice idea.   But it
>>would cause issues for people currently in the realm.   Their posts
>>would essentially dissapear from site and cause more confusion then its
>>worth.
>
>
> No they won't. Say you have a base table and your current post table is child
> of that. You can query on base table and get rows from child table. That way
> all the data would always be there.
>
> While inserting posts, you would insert in child table. While qeurying you
> would query on base table. That way things will be optimal.
>
>
>>Inheritance would work, but the database would essentially just grow and
>>grow and grow right?
>
>
> Right. But there are two advantages.
>
> 1. It will always contain valid posts. No dead tuples.
> 2. You can work in chuncks of data. Each child table can be dealt with
> separately without affecting other child tables, whereas in case of a single
> large table, entire site is affected..
>
> Deleting 100K posts from 101K rows table is vastly different than deleting 10K
> posts from 2M rows table. Later one would unnecessary starve the table with
> dead tuples and IO whereas in former case you can do create table as select
> from and drop the original..
>
> HTH
>
> Bye
>  Shridhar
>
> --
> "[In 'Doctor' mode], I spent a good ten minutes telling Emacs what Ithought of
> it.  (The response was, 'Perhaps you could try to be lessabusive.')"(By Matt
> Welsh)
>

When I ran EXPLAIN on the views and queries making use of the inherited
tables, I noticed that everything worked based on sequence scans and it
avoided all indexes.    While making use of ONLY kicked in full indexes.

This is even after having created a child table with the same indexes as
the parent.   Is this a known issue, or just some sort of oddity on my
setup?

Tables still cannot be removed easily, but I found a way to work around
it for a day-to-day basis.  Essentailly I just clean out the tables
containing old rows and delete them later.  However based on the above,
I doubt performance would get any better.

Thanks for the advice however!

    Martin Foster
    Creator/Designer Ethereal Realms
    martin@ethereal-realms.org



pgsql-performance by date:

Previous
From: Rod Taylor
Date:
Subject: Re: force the use of a particular index
Next
From: Tom Lane
Date:
Subject: Re: force the use of a particular index