Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6353F6348FD for ; Fri, 27 Feb 2009 16:18:51 -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 28165-06 for ; Fri, 27 Feb 2009 16:18:45 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f168.google.com (mail-fx0-f168.google.com [209.85.220.168]) by mail.postgresql.org (Postfix) with ESMTP id 201F7635DD3 for ; Fri, 27 Feb 2009 16:18:44 -0400 (AST) Received: by fxm12 with SMTP id 12so1689811fxm.19 for ; Fri, 27 Feb 2009 12:18:42 -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=DiAfQsudiElsJaEh43HfIHHwh5og4DAQcmjkGHO7H7U=; b=cYwPa9FCrLj4tzChJ0QOs3cw3I6g5aLfVo7VmbSZIRV3MDShScLyW1olpU6jTpHmcC wENM2HgjTdIPi4bRdLZi/ii6cyR36WDeAmxyebOtf98mLlzR5NUqOQZAoJIn1i2/oDz4 cY1K0qQ06vVDsnK+pWvbRXPtTtNh0dom1AzOA= 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=ML0qmOsLKYqrfKM010wDvchYv5jJI8b5hKlJw/GC3PhnRkiyLWx+nu8gfDgT2++kuL wJDhqNxzzs918TEY1kuaZEngw/G11dEVQWy3kBKoHgm5m8Q7agAVXD9T9RzPJIqhsZp1 cGOJYEHzxmf6/IU1K+SDOgcCXLKmdGgRlTpY8= MIME-Version: 1.0 Received: by 10.181.5.14 with SMTP id h14mr996011bki.22.1235765922228; Fri, 27 Feb 2009 12:18:42 -0800 (PST) In-Reply-To: <88daf38c0902150845y2878fcbfmcd06d426b53a886a@mail.gmail.com> References: <88daf38c0902141425s6972d849i9dad904f55d93c77@mail.gmail.com> <88daf38c0902150845y2878fcbfmcd06d426b53a886a@mail.gmail.com> Date: Fri, 27 Feb 2009 21:18:42 +0100 X-Google-Sender-Auth: 81e4f78d88f68a7b Message-ID: <88daf38c0902271218p1931ec9dyfdc269bceab24190@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: quoted-printable 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/336 X-Sequence-Number: 32917 On Sun, Feb 15, 2009 at 5:45 PM, Alexander Staubo wrote: > On Sun, Feb 15, 2009 at 5:29 AM, David Wilson = wrote: >> On Sat, Feb 14, 2009 at 5:25 PM, Alexander Staubo wrot= e: >>> >>> Output from "explain analyze": >>> >>> =C2=A0Limit =C2=A0(cost=3D0.00..973.63 rows=3D4 width=3D48) (actual >>> time=3D61.554..4039.704 rows=3D1 loops=3D1) >>> =C2=A0 -> =C2=A0Nested Loop =C2=A0(cost=3D0.00..70101.65 rows=3D288 wid= th=3D48) (actual >>> time=3D61.552..4039.700 rows=3D1 loops=3D1) >>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 -> =C2=A0Nested Loop =C2=A0(cost=3D0.00..68= 247.77 rows=3D297 width=3D52) >>> (actual time=3D61.535..4039.682 rows=3D1 loops=3D1) >> >> 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 right, this query keeps coming back to bite me. If this part of the joi= n: ... and section_items.sandbox_id =3D 16399 yields a sufficiently large number of matches, then performance goes 'boink', like so: Limit (cost=3D0.00..34.86 rows=3D4 width=3D48) (actual time=3D4348.696..4348.696 rows=3D0 loops=3D1) -> Nested Loop (cost=3D0.00..60521.56 rows=3D6944 width=3D48) (actual time=3D4348.695..4348.695 rows=3D0 loops=3D1) -> Index Scan using index_event_occurrences_on_start_time on event_occurrences (cost=3D0.00..11965.38 rows=3D145712 width=3D48) (actual time=3D0.093..138.029 rows=3D145108 loops=3D1) Index Cond: (start_time > '2009-02-27 18:01:14.739411+01'::timestamp with time zone) -> Index Scan using index_section_items_on_subject_type_and_subject_id on section_items (cost=3D0.00..0.32 rows=3D1 width=3D4) (actual time=3D0.029..0.029 rows=3D0 loops=3D145108) Index Cond: (((section_items.subject_type)::text =3D 'Event'::text) AND (section_items.subject_id =3D event_occurrences.event_id)) Filter: (section_items.sandbox_id =3D 9) Total runtime: 4348.777 ms In this case: # select count(*) from section_items where sandbox_id =3D 9; count ------- 3126 If I remove the start_time > ... clause, performance is fine. Upping the statistics setting on any of the columns involved seems to have no effect. Is this a pathological border case, or is there something I can do to *generally* make this query run fast? Keep in mind that the query itself returns no rows at all. I want to avoid doing an initial "select count(...)" just to avoid the bad plan. Suffice to say, having a web request take 5 seconds is asking too much from our users. Alexander.