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;
	b=JXNkUKrj6mQ/+fQJM3IFAHLA7zbzNHIPM9urEuytMefbjtpuFEOu98GNL2gxYRL/RtitTlj1u1MGPKXty1YYe9+VC7h3OqYhBJky7JFmd/iUc/jI3aaOF3TxI6dRenpNFnLoOBpu+Sl9sLqVtmHdlIUKJuY8amnDQUrqS6OTjJQ=
Message-ID: <46A5BBD4.8040509@gmail.com>
Date: Tue, 24 Jul 2007 18:44:04 +1000
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 2.0.0.5 (Windows/20070716)
MIME-Version: 1.0
To: Jonathan Gray <jgray@streamy.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Query performance issue
References: <0f1701c7cdc6$f9cc1a30$ed644e90$@com>
In-Reply-To: <0f1701c7cdc6$f9cc1a30$ed644e90$@com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Jonathan Gray wrote:
> We’re experiencing a query performance problem related to the planner 
> and its ability to perform a specific type of merge.
> 
>  
> 
> We have created a test case (as attached, or here: 
> http://www3.streamy.com/postgres/indextest.sql) which involves a 
> hypothetical customer ordering system, with customers, orders, and 
> customer groups.
> 
>  
> 
> If we want to retrieve a single customers 10 most recent orders, sorted 
> by date, we can use a double index on (customer,date); Postgres’s query 
> planner will use the double index with  a backwards index scan on the 
> second indexed column (date).
> 
>  
> 
> However, if we want to retrieve a “customer class’s” 10 most recent 
> orders, sorted by date, we are not able to get Postgres to use double 
> indexes.

You don't have any indexes on the 'customerclass' table.

Creating a foreign key doesn't create an index, you need to do that 
separately.

Try

create index cc_customerid_class on indextest.customerclass(classid, 
customerid);

-- 
Postgresql & php tutorials
http://www.designmagick.com/