Skip site navigation (1) Skip section navigation (2)

Re: Bad plan for nested loop + limit

From: Alexander Staubo <alex(at)bengler(dot)no>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Bad plan for nested loop + limit
Date: 2009-02-28 16:20:22
Message-ID: 88daf38c0902280820p745ea234s85efa7f5e58bd0ed@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, Feb 27, 2009 at 11:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> The problem here is that the planner estimates the cost of a Limit
> plan node by adding up (1) the startup cost of the underlying plan
> node, in this case 0 for the nestjoin, and (2) a percentage of the run
> cost, based on the ratio of the number of rows expected to be returned
> to the total number of rows.  In this case, the nested loop is
> expected to return 6944 rows, so it figures it won't have to get very
> far to find the 4 you requested.
[...]
> I will think about this some more but nothing is occurring to me off
> the top of my head.

Thanks for explaining. Is there any way to rewrite the query in a way
that will avoid the nested loop join -- other than actually disabling
nested loop joins? If I do the latter, the resulting query uses a hash
join and completes in 80-100 ms, which is still pretty horrible,
especially for a query that returns nothing, but extremely auspicious
compared to the unthinkable 4-5 seconds for the current query.

Alexander.

In response to

Responses

pgsql-performance by date

Next:From: Brian CoxDate: 2009-03-01 02:51:32
Subject: "slow" queries
Previous:From: Robert HaasDate: 2009-02-27 22:54:47
Subject: Re: Bad plan for nested loop + limit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group