Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 99DA3133655C for ; Fri, 18 Mar 2011 03:58:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 96020-05 for ; Fri, 18 Mar 2011 06:58:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mailout.thl.fi (mailout.thl.fi [193.167.195.59]) by mail.postgresql.org (Postfix) with ESMTP id DAE9D133616B for ; Fri, 18 Mar 2011 03:58:07 -0300 (ADT) Received: from mailout (localhost.localdomain [127.0.0.1]) by mailout.thl.fi (8.13.1/8.13.1) with SMTP id p2I6w6fg029198 for ; Fri, 18 Mar 2011 08:58:06 +0200 Received: from 172.24.32.92 (172.24.32.92) by mailout (F-Secure/fsigk_smtp/580/mailout); Fri, 18 Mar 2011 08:58:06 +0200 (EET) X-Virus-Status: clean(F-Secure/fsigk_smtp/580/mailout) Received: from exhub1.stakes.fi (exhub1.stakes.fi [10.10.3.203]) by mail-hub.thl.fi (8.13.8/8.13.8) with ESMTP id p2I6w619011617 for ; Fri, 18 Mar 2011 08:58:06 +0200 Received: from [172.22.12.109] (172.22.12.109) by exhub1.stakes.fi (10.10.3.203) with Microsoft SMTP Server id 8.2.176.0; Fri, 18 Mar 2011 08:58:05 +0200 Message-ID: <4D8306A7.7020205@thl.fi> Date: Fri, 18 Mar 2011 09:15:51 +0200 From: =?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?= User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.14) Gecko/20110223 Thunderbird/3.1.8 MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Subject: Disabling nested loops - worst case performance Content-Type: text/plain; charset="ISO-8859-1"; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/203 X-Sequence-Number: 42882 Hello list, I am working on a Entity-Attribute-Value (EAV) database using PostgreSQL 8.4.7. The basic problem is that when joining multiple times different entities the planner thinks that there is vastly less rows to join than there is in reality and decides to use multiple nested loops for the join chain. This results in queries where when nested loops are enabled, query time is somewhere around 35 seconds, but with nested loops disabled, the performance is somewhere around 100ms. I don't think there is much hope for getting better statistics, as EAV is just not statistics friendly. The values of an attribute depend on the type of the attribute, and different entities have different attributes defined. The planner has no idea of these correlations. Now, my question is: if I disable nested loops completely for the users of the EAV database what kind of worst case performance loss can I expect? I don't mind if a query that normally runs in 100ms now takes 200ms, but about problems where the query will take much more time to complete than with nested loops enabled. As far as I understand these cases should be pretty rare if non-existent? - Anssi