Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C111063256C for ; Sun, 11 Apr 2010 23:44:15 -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 26457-01 for ; Mon, 12 Apr 2010 02:44:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.24]) by mail.postgresql.org (Postfix) with ESMTP id 2DCEC632E95 for ; Sun, 11 Apr 2010 23:44:05 -0300 (ADT) Received: by qw-out-2122.google.com with SMTP id 8so1769496qwh.61 for ; Sun, 11 Apr 2010 19:44:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:references:in-reply-to :content-type; bh=nUi0Fju9RVRkZOCHX8PK/UQKJPOytAYMYl7KaAHMaNU=; b=qVb+TuBvdoBsTgqyTqNQCW+KiY5Q134vtFxWQYaXHE4RM8brwGerVjZIHZDULfHY5h syHxv9RNzkgmt5WQwvDJK8QTJEgl/EejokJF7M1unaVsq+TJA8ByE4UfLEG7diQWW/Gk 7S3Lz9z8Jtvoxj4Q5ap58qLeRUX/q6GHKJ7zM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type; b=c+wwwxROKqqzAXRYZd8LXg9FPbopsxugroHfkmRJOPtBkE46xYAZ+UOwyfPIuEGarK 0AEVEOMbJzexYD8x+eiRqJjzYGzPTHFILoIXsufXllPe9OvT+laaawNSYEQe7yI4lyTJ hKIrkZyhBwHUVDmhuoP8IRjzWSFOIYj/jHyHc= Received: by 10.229.217.14 with SMTP id hk14mr5028327qcb.89.1271040243655; Sun, 11 Apr 2010 19:44:03 -0700 (PDT) Received: from [192.168.0.194] (c93479ce.virtua.com.br [201.52.121.206]) by mx.google.com with ESMTPS id f5sm5487091qcg.14.2010.04.11.19.44.00 (version=TLSv1/SSLv3 cipher=RC4-MD5); Sun, 11 Apr 2010 19:44:01 -0700 (PDT) Message-ID: <4bc288f1.0534e50a.5b97.ffffd4c7@mx.google.com> Date: Sun, 11 Apr 2010 23:43:57 -0300 From: Helio Campos Mello de Andrade User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: significant slow down with various LIMIT References: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com> In-Reply-To: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com> Content-Type: multipart/alternative; boundary="------------080103000800070102010903" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.012 tagged_above=-10 required=5 tests=FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201004/146 X-Sequence-Number: 38396 This is a multi-part message in MIME format. --------------080103000800070102010903 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 1 ) Limit (cost=0.00..9.57 rows=3 width=4) (actual time=*0.090..0.138* rows=3 loops=1) 2 ) Limit (cost=0.00..12.76 rows=4 width=4) (actual time=*0.091..4436.795* rows=4 loops=1) 1 ) -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual time=*0.088..0.136* rows=*3* loops=1) 2 ) -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual time=*0.089..4436.791* rows=*4* loops=1) 1 ) Merge Cond: (plugins_plugin_addr.oid_id = core_object.id) 2 ) Merge Cond: (plugins_plugin_addr.oid_id = core_object.id) 1 ) -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4) (actual time=*0.056..0.095* rows=*3* loops=1) 2 ) -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4) (actual time=*0.056..3988.249* rows=*4* loops=1) ################################################################################################################################################################################################### 1 ) -> Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=_*0.027..0.032*_ rows=*3* loops=1) 2 ) -> Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=_*0.027..329.942*_ rows=*1244476* loops=1) 1 ) -> Index Scan using plugins_guide_address_pkey on plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual time=_*0.017..0.018*_ rows=*1* loops=*3*) 2 ) -> Index Scan using plugins_guide_address_pkey on plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual time=_*0.003..0.003*_ rows=*0* loops=*1244476*) ################################################################################################################################################################################################### - I am not an expert in the matter but in the first query it took only 3 loops to find 1 row and in the second it looped 1244476 times to find no row at all. Is it possible that there is no other row in the table that match the data you are trying to retrieve? - Have you tried to recreate the index of the table? It could be that its damaged in some way that postgres can not use the index and its making a full search in the table. Again, it's just a wild guess. 1 ) Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id) 2 ) Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id) 1 ) Filter: (plugins_guide_address.city_id = 4535) 2 ) Filter: (plugins_guide_address.city_id = 4535) 1 ) -> Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=*0.026..0.028* rows=*3* loops=1) 2 ) -> Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=*0.027..284.195* rows=*1244479* loops=1) 1 ) Total runtime: 0.244 ms 2 ) Total runtime: 4436.894 ms Regards... -- Helio Campos Mello de Andrade --------------080103000800070102010903 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 1 ) Limit  (cost=0.00..9.57 rows=3 width=4) (actual time=0.090..0.138 rows=3 loops=1)
2 ) Limit  (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795 rows=4 loops=1)
1 )     ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual time=0.088..0.136 rows=3 loops=1)
2 )     ->  Merge Join  (cost=0.00..1098182.56 rows=344125 width=4) (actual time=0.089..4436.791 rows=4 loops=1)
1 )             Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
2 )             Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
 1 )                 ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4) (actual time=0.056..0.095 rows=3 loops=1)
2 )                 ->  Nested Loop  (cost=0.00..972804.02 rows=344125 width=4) (actual time=0.056..3988.249 rows=4 loops=1)
###################################################################################################################################################################################################
1 )                     ->  Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032 rows=3 loops=1)
2 )                     ->  Index Scan Backward using plugins_plugin_addr_oid_id on plugins_plugin_addr (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942 rows=1244476 loops=1)

1 )                     ->  Index Scan using plugins_guide_address_pkey on plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=3)
2 )                     ->  Index Scan using plugins_guide_address_pkey on plugins_guide_address  (cost=0.00..0.56 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1244476)
###################################################################################################################################################################################################
 - I am not an expert in the matter but in the first query it took only 3 loops to find 1 row and in the second it looped 1244476 times to find no row at all. Is it possible that there is no other row in the table that match the data you are trying to retrieve?
 - Have you tried to recreate the index of the table? It could be that its damaged in some way that postgres can not use the index and its making a full search in the table. Again, it's just a wild guess.

1 )                             Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id)
2 )                             Index Cond: (plugins_guide_address.id = plugins_plugin_addr.address_id)
 1 )                             Filter: (plugins_guide_address.city_id = 4535)
2 )                             Filter: (plugins_guide_address.city_id = 4535)
 1 )             ->  Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028 rows=3 loops=1)
2 )             ->  Index Scan using core_object_pkey_desc on core_object (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.027..284.195 rows=1244479 loops=1)
 1 ) Total runtime: 0.244 ms
2 ) Total runtime: 4436.894 ms

Regards...

--
Helio Campos Mello de Andrade

--------------080103000800070102010903--