Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B7D776330AC for ; Sun, 15 Feb 2009 00:29:55 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 58724-03 for ; Sun, 15 Feb 2009 00:29:52 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.29]) by mail.postgresql.org (Postfix) with ESMTP id AE8A4632E4C for ; Sun, 15 Feb 2009 00:29:52 -0400 (AST) Received: by yx-out-2324.google.com with SMTP id 3so831691yxj.73 for ; Sat, 14 Feb 2009 20:29:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=1EJTXWzr/ZnF5OmRSjMPQNuvzHSMlDdWsbJOc0bS++o=; b=iLqOA9ivT6PQkBmo1J9zi/fFMF0JNLyN2VAlAmhI6e+4d25RBlZBydXkaFciW0uhhb ZM9j05R7xYS50+ULX4IPkOWmOwrodaX4lAKFyeqy+Mf2OA9Wnev0T6IKWNqC5O+J2HXo gjWG3zzHLRN7R5WAkxS2njPcs7YRoLam0fquU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=XGdd4abKImm4vzqwkBy8h0l2ud3YXvsPY8b9MTdyjkI0Bg+c94zjFf0hmesbN6jNV4 7NUV9kyP+TW851FvT0lCMWAG98egTncJY77j7WW76MVjVvx5+Kz+35ospDSg6s2zPx7L KAxt71gfx924E23VYFh7baFbXjdFVD5HNAsoM= MIME-Version: 1.0 Received: by 10.151.157.1 with SMTP id j1mr3853883ybo.83.1234672192094; Sat, 14 Feb 2009 20:29:52 -0800 (PST) In-Reply-To: <88daf38c0902141425s6972d849i9dad904f55d93c77@mail.gmail.com> References: <88daf38c0902141425s6972d849i9dad904f55d93c77@mail.gmail.com> Date: Sat, 14 Feb 2009 23:29:52 -0500 Message-ID: Subject: Re: Bad plan for nested loop + limit From: David Wilson To: Alexander Staubo Cc: pgsql-performance Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/172 X-Sequence-Number: 32753 On Sat, Feb 14, 2009 at 5:25 PM, Alexander Staubo wrote: > > Output from "explain analyze": > > Limit (cost=0.00..973.63 rows=4 width=48) (actual > time=61.554..4039.704 rows=1 loops=1) > -> Nested Loop (cost=0.00..70101.65 rows=288 width=48) (actual > time=61.552..4039.700 rows=1 loops=1) > -> Nested Loop (cost=0.00..68247.77 rows=297 width=52) > (actual time=61.535..4039.682 rows=1 loops=1) Those estimates are pretty far off. Did you try increasing the statistics target? Also, is the first query repeatable (that is, is it already in cache when you do the test, or alternately, are all queries *out* of cache when you test?) -- - David T. Wilson david.t.wilson@gmail.com