From: Clem Dickey <dickeycl@us.ibm.com>
Subject: Re: Planner choosing NestedLoop, although it is slower...
Date: Tue, 12 Jul 2011 12:55:14 -0700
Organization: Hub.Org Networking Services
Lines: 44
Message-ID: <ivi8r5$16se$1@news.hub.org>
References: <4E1C8E62.2090800@megafon.hr>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110621 Fedora/3.1.11-1.fc14 Thunderbird/3.1.11
In-Reply-To: <4E1C8E62.2090800@megafon.hr>
To: pgsql-performance@postgresql.org

On 07/12/2011 11:11 AM, Mario Splivalo wrote:
> Hi, all.
>
> I have a query, looking like this:
> SELECT
> pub_date
> FROM
> tubesite_object
> INNER JOIN tubesite_image
> ON tubesite_image.object_ptr_id = tubesite_object.id
> WHERE
> tubesite_object.site_id = 8
> AND tubesite_object.pub_date < E'2011-07-12 13:25:00'
> ORDER BY
> tubesite_object.pub_date ASC
> LIMIT 21;
>

> Why is planner using NestedLoops, that is, what can I do to make him NOT
> to use NestedLoops (other than issuing SET enable_nestloop TO false;
> before each query) ?

The planner is using a nested loops because the startup overhead is 
less, and it think that it will only have run a small 0.2% (21/9404) of 
the loops before reaching your limit of 21 results. In fact it has to 
run all the loops, because there are 0 results. (Is that what you expected?)

Try a using CTE to make the planner think you are going to use all the 
rows of the joined table. That may cause the planner to use a merge 
join, which has higher startup cost (sort) but less overall cost if it 
the join will not finish early.

WITH t AS (
   SELECT tubesite_object.site_id AS site_id,
     tubesite_object.pub_date as pub_date
   FROM tubesite_object
   INNER JOIN tubesite_image
   ON tubesite_image.object_ptr_id = tubesite_object.id
)
SELECT pub_date
FROM t
WHERE t.site_id = 8 AND t.pub_date < E'2011-07-12 13:25:00'
ORDER BY t.pub_date ASC LIMIT 21;