Re: postgresql meltdown on PlanetMath.org - Mailing list 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@familyhealth.com.au>
>To: "Kendrick C. Wilson"
><kendrick_wilson@hotmail.com>,<pgsql-performance@postgresql.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@postgresql.org>; Mon, 17 Mar 2003 20:34:33 -0500 (EST)
>Received: (from root@localhost)by houston.familyhealth.com.au
>(8.11.6/8.11.6) id h2I1Yac95711for pgsql-performance@postgresql.org; Tue,
>18 Mar 2003 09:34:36 +0800 (WST)(envelope-from chriskl@familyhealth.com.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@postgresql.org
>Message-ID: <07b501c2ecee$8917b8c0$6500a8c0@fhp.internal>
>References: <BAY1-F104L3frQfGvB200019147@hotmail.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@postgresql.org
>Return-Path: pgsql-performance-owner+M1426@postgresql.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