Re: postgresql meltdown on PlanetMath.org

From: "Kendrick C(dot) Wilson" <kendrick_wilson(at)hotmail(dot)com>
To: chriskl(at)familyhealth(dot)com(dot)au, pgsql-performance(at)postgresql(dot)org
Subject: Re: postgresql meltdown on PlanetMath.org
Date: 2003-03-18 15:19:44
Message-ID: BAY1-F1026QR7j1HaKt0003f672@hotmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Clustering is good for queries that return multiple values.

select this, that
from tableA
where this = 'whatever';

If there are multiple values, the location of the first record is found in
the indexFile.

Then dataFile is scanned until this != 'whatever';

This will decrease disk activity, which is the bottle neck in database
performance.

k=n^r/ck, SCJP

>From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
>To: "Kendrick C. Wilson"
><kendrick_wilson(at)hotmail(dot)com>,<pgsql-performance(at)postgresql(dot)org>
>Subject: Re: [PERFORM] postgresql meltdown on PlanetMath.org Date: Tue, 18
>Mar 2003 09:34:36 +0800
>MIME-Version: 1.0
>Received: from relay2.pgsql.com ([64.49.215.143]) by
>mc6-f41.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Mon, 17 Mar
>2003 17:34:42 -0800
>Received: from postgresql.org (postgresql.org [64.49.215.8])by
>relay2.pgsql.com (Postfix) with ESMTPid 022ADE5BD; Mon, 17 Mar 2003
>20:34:36 -0500 (EST)
>Received: from houston.familyhealth.com.au (unknown [203.59.48.253])by
>postgresql.org (Postfix) with ESMTP id A55E5475F09for
><pgsql-performance(at)postgresql(dot)org>; Mon, 17 Mar 2003 20:34:33 -0500 (EST)
>Received: (from root(at)localhost)by houston.familyhealth.com.au
>(8.11.6/8.11.6) id h2I1Yac95711for pgsql-performance(at)postgresql(dot)org; Tue,
>18 Mar 2003 09:34:36 +0800 (WST)(envelope-from chriskl(at)familyhealth(dot)com(dot)au)
>Received: from mariner (mariner.internal [192.168.0.101])by
>houston.familyhealth.com.au (8.11.6/8.9.3) with SMTP id h2I1YW795594;Tue,
>18 Mar 2003 09:34:32 +0800 (WST)
>X-Message-Info: yilqo4+6kc64AXpUCzRAW30W84h6gtv8
>X-Original-To: pgsql-performance(at)postgresql(dot)org
>Message-ID: <07b501c2ecee$8917b8c0$6500a8c0(at)fhp(dot)internal>
>References: <BAY1-F104L3frQfGvB200019147(at)hotmail(dot)com>
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Mailer: Microsoft Outlook Express 6.00.2800.1106
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
>X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/)
>Precedence: bulk
>Sender: pgsql-performance-owner(at)postgresql(dot)org
>Return-Path: pgsql-performance-owner+M1426(at)postgresql(dot)org
>X-OriginalArrivalTime: 18 Mar 2003 01:34:42.0860 (UTC)
>FILETIME=[8CCFDEC0:01C2ECEE]
>
>
>
> > What is the structure of you table?
> > Is the data types in the table the same as in the SQL....
> >
> > Did you create the index after the loading the table?
> > cluster the table around the most used index....
>
>There is no point clustering a table around the most used index, unless
>access to the index is non-random. eg. you are picking up more than one
>consecutive entry from the index at a time. eg. Indexes on foreign keys
>are
>excellent for clustering.
>
>Chris
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2003-03-18 16:26:10 Re: postgresql meltdown on PlanetMath.org
Previous Message Chris Sutton 2003-03-18 14:59:56 Re: postgresql meltdown on PlanetMath.org