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=apyqIZnZk3ozXZVFy7NR1dm2asp5HEn3+FcrS2FSzhhZpMz8iqdJrHwtaSgVIIZa+gommSiQ+FGyFRLGOUtluIn4r788aYXQMMZ6zpwhPVULY8DmAgtJxg+sE0d78fA4GpJ9GVvz0nxuLX9rfVeLpOpzcoK4qJvaVep1lMoTdG0=
Message-ID: <46A5C819.80707@gmail.com>
Date: Tue, 24 Jul 2007 19:36:25 +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> <46A5BBD4.8040509@gmail.com>
	<46A5BD6F.7000108@gmail.com> <0f3001c7cdd3$a7db7740$f79265c0$@com>
In-Reply-To: <0f3001c7cdd3$a7db7740$f79265c0$@com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jonathan Gray wrote:
> Chris,
> 
> Creating indexes on the customerclass table does speed up the queries but
> still does not create the plan we are looking for (using the double index
> with a backward index scan on the orders table).

Stupid question - why is that particular plan your "goal" plan?

> The plans we now get, with times on par or slightly better than with the
> plpgsql hack, are:
> 
>   EXPLAIN ANALYZE
>   SELECT o.orderid,o.orderstamp FROM indextest.orders o 
>   INNER JOIN indextest.customerclass cc ON (cc.classid = 2) 
>   WHERE o.customerid = cc.customerid ORDER BY o.orderstamp DESC LIMIT 5;

Didn't notice this before...

Shouldn't this be:

INNER JOIN indextest.customerclass cc ON (o.customerid = cc.customerid)
WHERE cc.classid = 2

ie join on the common field not the classid one which doesn't appear in 
the 2nd table?

> As I said, this is a hypothetical test case we have arrived at that
> describes our situation as best as we can given a simple case.  We're
> interested in potential issues with the approach, why postgres would not
> attempt something like it, and how we might go about implementing it
> ourselves at a lower level than we currently have (in SPI, libpq, etc). 
> 
> If it could be generalized then we could use it in cases where we aren't
> pulling from just one table (the orders table) but rather trying to merge,
> in sorted order, results from different conditions on different tables.
> Right now we use something like the plpgsql or plpythonu functions in the
> example and they outperform our regular SQL queries by a fairly significant
> margin.

I'm sure if you posted the queries you are running with relevant info 
you'd get some help ;)

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