From: Martin Foster <martin@ethereal-realms.org>
To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: [NOVICE] Extreme high load averages
Date: Fri, 11 Jul 2003 00:09:38 -0600
Message-ID: <3F0E54A2.4040604@ethereal-realms.org> (raw)
In-Reply-To: <3F0D5EEC.29862.506A9AE@localhost>
References: <3F0D5550.21678.4E122CF@localhost>
<3F0D5EEC.29862.506A9AE@localhost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: martin@ethereal-realms.org, shridhar_daithankar@persistent.co.in
Subject: Re: [NOVICE] Extreme high load averages
In-Reply-To: <3F0E54A2.4040604@ethereal-realms.org>
References: <3F0D5550.21678.4E122CF@localhost> <3F0D5EEC.29862.506A9AE@localhost> <3F0E54A2.4040604@ethereal-realms.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox