From: Alexander Staubo <alex@bengler.no>
To: Robert Haas <robertmhaas@gmail.com>
Cc: David Wilson <david.t.wilson@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Bad plan for nested loop + limit
Date: Sat, 28 Feb 2009 17:20:22 +0100
Message-ID: <88daf38c0902280820p745ea234s85efa7f5e58bd0ed@mail.gmail.com> (raw)
In-Reply-To: <603c8f070902271454h5a78a063kcf2b78a10fd956b1@mail.gmail.com>
References: <88daf38c0902141425s6972d849i9dad904f55d93c77@mail.gmail.com>
<e7f9235d0902142029h5b53baf2gede92b22afbbad35@mail.gmail.com>
<88daf38c0902150845y2878fcbfmcd06d426b53a886a@mail.gmail.com>
<88daf38c0902271218p1931ec9dyfdc269bceab24190@mail.gmail.com>
<603c8f070902271454h5a78a063kcf2b78a10fd956b1@mail.gmail.com>
On Fri, Feb 27, 2009 at 11:54 PM, Robert Haas <robertmhaas@gmail.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alex@bengler.no, robertmhaas@gmail.com, david.t.wilson@gmail.com
Subject: Re: Bad plan for nested loop + limit
In-Reply-To: <88daf38c0902280820p745ea234s85efa7f5e58bd0ed@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox