Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A9F98634254 for ; Fri, 9 Apr 2010 19:49:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 32511-03 for ; Fri, 9 Apr 2010 22:48:53 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gw0-f46.google.com (mail-gw0-f46.google.com [74.125.83.46]) by mail.postgresql.org (Postfix) with ESMTP id 2C8426324E2 for ; Fri, 9 Apr 2010 19:48:54 -0300 (ADT) Received: by gwj19 with SMTP id 19so41507gwj.19 for ; Fri, 09 Apr 2010 15:48:52 -0700 (PDT) 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:received:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=aq3w1Eh1MUpNmmVfkRAA+d+FNaWwQ/6qI6+qJJPhNhY=; b=wFiEuHwdpcMa6niGZOX5umDAUUhf2kZgjlKSj6DOswoPxQpLXgNcXRyN6UMISG1jl4 88GMFug78Q/x+WZojC+nDB/hcYDydiKnFYKPNbZnH7M4HJmVieeo70caKco5nnd1cdsW HKI9zLuWoCw+V8br5Q+egBzOUx4lnoK853WD8= 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=PZ+KILM+00DTYo8TB391NF31DWBvzAulLbTBj1AViziPA/cIVJhw+vu0q06ISDWyB+ 6oyYz+6BsiAVW0814VZKwQ1rDHvkugM753SttpaOOqkxT5xwqMLvEg85ABB1eknOTa1S 8HQRKaFUF2xw4UXm0n00e0+avqRKqAZ82bZPo= MIME-Version: 1.0 Received: by 10.231.19.69 with HTTP; Fri, 9 Apr 2010 15:48:52 -0700 (PDT) In-Reply-To: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com> References: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com> Date: Fri, 9 Apr 2010 18:48:52 -0400 Received: by 10.101.178.20 with SMTP id f20mr1203582anp.229.1270853332707; Fri, 09 Apr 2010 15:48:52 -0700 (PDT) Message-ID: Subject: Re: significant slow down with various LIMIT From: Robert Haas To: norn Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201004/129 X-Sequence-Number: 38379 On Tue, Apr 6, 2010 at 8:42 PM, norn wrote: > I have some mysterious slow downs with ORDER BY and LIMIT. When LIMIT > getting greater than some value (greater than 3 in my case), query > takes 4-5 secs instead of 0.25ms. All of the necessary indexes are in > place. I have no idea what to do, so any advices are welcome! > > Here my queries and explain analyzes; > > First Query with LIMIT 3 (fast) > ------------- > explain analyze SELECT core_object.id from "core_object" INNER JOIN > "plugins_plugin_addr" ON ("core_object"."id" =3D > "plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON > ("plugins_plugin_addr"."address_id" =3D "plugins_guide_address"."id") > WHERE "plugins_guide_address"."city_id" =3D 4535 =A0ORDER BY > "core_object"."id" DESC LIMIT 3; > > =A0Limit =A0(cost=3D0.00..9.57 rows=3D3 width=3D4) (actual time=3D0.090..= 0.138 > rows=3D3 loops=3D1) > =A0 -> =A0Merge Join =A0(cost=3D0.00..1098182.56 rows=3D344125 width=3D4)= (actual > time=3D0.088..0.136 rows=3D3 loops=3D1) > =A0 =A0 =A0 =A0 Merge Cond: (plugins_plugin_addr.oid_id =3D core_object.i= d) > =A0 =A0 =A0 =A0 -> =A0Nested Loop =A0(cost=3D0.00..972804.02 rows=3D34412= 5 width=3D4) > (actual time=3D0.056..0.095 rows=3D3 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan Backward using > plugins_plugin_addr_oid_id on plugins_plugin_addr > (cost=3D0.00..52043.06 rows=3D1621103 width=3D8) (actual time=3D0.027..0.= 032 > rows=3D3 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using plugins_guide_address_= pkey on > plugins_guide_address =A0(cost=3D0.00..0.56 rows=3D1 width=3D4) (actual > time=3D0.017..0.018 rows=3D1 loops=3D3) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: (plugins_guide_addres= s.id =3D > plugins_plugin_addr.address_id) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (plugins_guide_address.ci= ty_id =3D 4535) > =A0 =A0 =A0 =A0 -> =A0Index Scan using core_object_pkey_desc on core_obje= ct > (cost=3D0.00..113516.08 rows=3D3091134 width=3D4) (actual time=3D0.026..0= .028 > rows=3D3 loops=3D1) > =A0Total runtime: 0.244 ms > (10 rows) > > Second Query, the same, but with LIMIT 4 (slooooow) > ------------- > explain analyze SELECT core_object.id from "core_object" INNER JOIN > "plugins_plugin_addr" ON ("core_object"."id" =3D > "plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON > ("plugins_plugin_addr"."address_id" =3D "plugins_guide_address"."id") > WHERE "plugins_guide_address"."city_id" =3D 4535 =A0ORDER BY > "core_object"."id" DESC LIMIT 4; > > =A0Limit =A0(cost=3D0.00..12.76 rows=3D4 width=3D4) (actual time=3D0.091.= .4436.795 > rows=3D4 loops=3D1) > =A0 -> =A0Merge Join =A0(cost=3D0.00..1098182.56 rows=3D344125 width=3D4)= (actual > time=3D0.089..4436.791 rows=3D4 loops=3D1) > =A0 =A0 =A0 =A0 Merge Cond: (plugins_plugin_addr.oid_id =3D core_object.i= d) > =A0 =A0 =A0 =A0 -> =A0Nested Loop =A0(cost=3D0.00..972804.02 rows=3D34412= 5 width=3D4) > (actual time=3D0.056..3988.249 rows=3D4 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan Backward using > plugins_plugin_addr_oid_id on plugins_plugin_addr > (cost=3D0.00..52043.06 rows=3D1621103 width=3D8) (actual time=3D0.027..32= 9.942 > rows=3D1244476 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using plugins_guide_address_= pkey on > plugins_guide_address =A0(cost=3D0.00..0.56 rows=3D1 width=3D4) (actual > time=3D0.003..0.003 rows=3D0 loops=3D1244476) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: (plugins_guide_addres= s.id =3D > plugins_plugin_addr.address_id) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (plugins_guide_address.ci= ty_id =3D 4535) > =A0 =A0 =A0 =A0 -> =A0Index Scan using core_object_pkey_desc on core_obje= ct > (cost=3D0.00..113516.08 rows=3D3091134 width=3D4) (actual > time=3D0.027..284.195 rows=3D1244479 loops=3D1) > =A0Total runtime: 4436.894 ms > (10 rows) What do you get with no LIMIT at all? ...Robert