Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id D88FD2E3E76 for ; Fri, 30 Nov 2007 07:18:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 46552-07 for ; Fri, 30 Nov 2007 07:18:20 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 60B3D2E3E73 for ; Fri, 30 Nov 2007 07:18:20 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id lAUBIFBX076506 for ; Fri, 30 Nov 2007 07:18:15 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id lAUBG7td076256 for pgsql-performance@postgresql.org; Fri, 30 Nov 2007 07:16:07 -0400 (AST) (envelope-from news) From: cluster X-Newsgroups: pgsql.performance Subject: Appending "LIMIT" to query drastically decreases performance Date: Fri, 30 Nov 2007 12:16:08 +0100 Organization: Hub.Org Networking Services Lines: 8 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Thunderbird 2.0.0.6 (X11/20071022) To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/396 X-Sequence-Number: 27811 Can anyone explain the following odd behavior? I have a query that completes in about 90 ms. If I append LIMIT to the very end, eg. "LIMIT 500" the evaluation time increases to about 800 ms. How can performance get *worse* by giving the database the option to stop the evaluation earlier (when it reaches the output 500 rows)? I have pasted both queries together with output from explain analyze here: http://pastebin.com/m3c0d1896