pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (22+ messages)

Message-ID: <3F0E54A2.4040604@ethereal-realms.org>
Permalink:  ../3F0E54A2.4040604@ethereal-realms.org/
Also on:    postgresql.org/message-id/3F0E54A2.4040604@ethereal-realms.org

 · 

reply

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