Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BC389632C51 for ; Sun, 15 Feb 2009 12:45:47 -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 18299-03 for ; Sun, 15 Feb 2009 12:45:44 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f170.google.com (mail-bw0-f170.google.com [209.85.218.170]) by mail.postgresql.org (Postfix) with ESMTP id 48EE5632AEF for ; Sun, 15 Feb 2009 12:45:44 -0400 (AST) Received: by bwz18 with SMTP id 18so3319181bwz.19 for ; Sun, 15 Feb 2009 08:45:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:sender:received:in-reply-to :references:date:x-google-sender-auth:message-id:subject:from:to:cc :content-type:content-transfer-encoding; bh=tnMZbIyGvX6X6CnTxw5LI2qGx0m9wO8ku7LNwtCh2Co=; b=JgoZx0Q7UG4VasH36jzQaU5Mjl2kGF4YVWQeNtukFG1014twJnwgR7YhFli6rE9fim kpnGV9c8DiuRbcUApR6Bg1pfKQ/yYDDdTRibdwleEzVExzgT07fg//JuYxPfzBhv2su0 IIdoopKqcLj+NbWm0Oi3S9+BYmUMbTc3lj+0Y= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type :content-transfer-encoding; b=UFpzCajRGbC3iNEdCuxllu63YhvPETJeUqZWvu4O88qs8GVW9mE3tRrVnH1PwGDYYF HoEj/HRT6lQV+y7yI+TXBt3+8HnAppS/PzpqDa9+ZiPIauKNJhH1ChJYN7TGajnP3KZ5 mTb5ef31Rd2sMOdL/Sn6zDJtwP36TE6wp6xdY= MIME-Version: 1.0 Received: by 10.180.213.14 with SMTP id l14mr948509bkg.107.1234716342931; Sun, 15 Feb 2009 08:45:42 -0800 (PST) In-Reply-To: References: <88daf38c0902141425s6972d849i9dad904f55d93c77@mail.gmail.com> Date: Sun, 15 Feb 2009 17:45:42 +0100 X-Google-Sender-Auth: 91a00d82ea38d495 Message-ID: <88daf38c0902150845y2878fcbfmcd06d426b53a886a@mail.gmail.com> Subject: Re: Bad plan for nested loop + limit From: Alexander Staubo To: David Wilson Cc: pgsql-performance Content-Type: text/plain; charset=UTF-8 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/173 X-Sequence-Number: 32754 On Sun, Feb 15, 2009 at 5:29 AM, David Wilson wrote: > 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?) All in the cache when I do the test. Ok, so upping the statistics to 100 on section_items.subject_id fixed it: Limit (cost=3530.95..3530.96 rows=4 width=48) (actual time=0.107..0.107 rows=1 loops=1) -> Sort (cost=3530.95..3531.12 rows=66 width=48) (actual time=0.106..0.106 rows=1 loops=1) Sort Key: event_occurrences.start_time Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=0.00..3529.96 rows=66 width=48) (actual time=0.098..0.100 rows=1 loops=1) -> Index Scan using index_section_items_on_sandbox_id on section_items (cost=0.00..104.29 rows=22 width=4) (actual time=0.017..0.033 rows=7 loops=1) Index Cond: (sandbox_id = 16399) Filter: ((subject_type)::text = 'Event'::text) -> Index Scan using index_event_occurrences_on_event_id on event_occurrences (cost=0.00..154.79 rows=74 width=48) (actual time=0.008..0.008 rows=0 loops=7) Index Cond: (event_occurrences.event_id = section_items.subject_id) Filter: (event_occurrences.start_time > '2009-02-14 18:15:14.739411+01'::timestamp with time zone) Total runtime: 0.142 ms Thanks. Alexander.