Subject: Re: Query planner unaware of possibly best plan
From: Simon Riggs <simon@2ndquadrant.com>
To: Denes Daniel <panther-d@freemail.hu>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <freemail.20070821173625.87009@fm03.freemail.hu>
References: <freemail.20070821173625.87009@fm03.freemail.hu>
Content-Type: text/plain
Date: Fri, 21 Sep 2007 18:49:33 +0100
Message-Id: <1190396973.4202.116.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2007-09-21 at 17:36 +0200, Denes Daniel wrote:

> Even though I forced the nested loop plan using both indexes (that 
> returns the rows in the correct order), there is a needless sort step on 
> the top, consuming half of the time even on such small tables.
> Now it's clear why the planner did not choose this plan, why I had to 
> force it: because it isn't the best if the sort is still there.

Ordering by parent, child is fairly common but the variation you've got
here isn't that common. You'd need to make a case considering all the
alternatives; nobody will agree without a balanced case that includes
what is best for everyone.

Your EXPLAIN looks edited. Have you also edited the sort costs? They
look slightly higher than we might expect. Please provide the full
normal EXPLAIN output.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com