DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=q4SsAVH6t/+nwFOiUkoSvE61LCjHjjPcDbw5ejRNDn8sNlkxgEfeA4D48UO9eIY1WBUqjR45uM79z7BbH0HSyXVXJKcGHr4PM5R1/9/qZj2ByDvc9+uCw3VX4bvdXLJ3cdhbTeXJbY74Xuu1msmNk1a3mRwYwj4Um7t8gulxCx8=
Message-ID: <473DF073.1000503@enterprisedb.com>
Date: Fri, 16 Nov 2007 19:33:07 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.12 (X11/20070730)
MIME-Version: 1.0
To: adrobj <adrobj@yahoo.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Clustered/covering indexes (or lack thereof :-)
References: <13700848.post@talk.nabble.com>
In-Reply-To: <13700848.post@talk.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

adrobj wrote:
> This is probably a FAQ, but I can't find a good answer...
> 
> So - are there common techniques to compensate for the lack of
> clustered/covering indexes in PostgreSQL? To be more specific - here is my
> table (simplified):
> 
> topic_id int
> post_id int
> post_text varchar(1024)
> 
> The most used query is: SELECT post_id, post_text FROM Posts WHERE
> topic_id=XXX. Normally I would have created a clustered index on topic_id,
> and the whole query would take ~1 disk seek.
> 
> What would be the common way to handle this in PostgreSQL, provided that I
> can't afford 1 disk seek per record returned?

You can cluster the table, see 
http://www.postgresql.org/docs/8.2/interactive/sql-cluster.html.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com