Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0426C9FB56A for ; Mon, 12 Feb 2007 19:26:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 98300-06 for ; Mon, 12 Feb 2007 19:26:31 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id CA92B9FB32A for ; Mon, 12 Feb 2007 19:26:30 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so48072nfb for ; Mon, 12 Feb 2007 15:26:29 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=tGdAOsQBI7lUhKHkvt7zBKHaXte0f0N10u2i4NJ+HkqHYQN7LgRfUcXLJSgb6Dkex/JZBmrBmKFSTzKpKv4dqaeKxvJhBI0PQTatLbm2MeA+++eKzMF6VJO7NfdOWv8k5Um40olGyYD7bnaMA0FZlWmsvXHt16S7T9xrb6rxpVU= Received: by 10.49.93.4 with SMTP id v4mr81771nfl.1171322789383; Mon, 12 Feb 2007 15:26:29 -0800 (PST) Received: from ?192.168.1.10? ( [82.3.238.153]) by mx.google.com with ESMTP id k9sm291621nfc.2007.02.12.15.26.26; Mon, 12 Feb 2007 15:26:28 -0800 (PST) Message-ID: <45D0F78C.40000@enterprisedb.com> Date: Mon, 12 Feb 2007 23:26:04 +0000 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.8 (X11/20061116) MIME-Version: 1.0 To: Brian Cox CC: pgsql-performance@postgresql.org Subject: Re: limit + order by is slow if no rows in result set References: <45D0DDED.1070400@ca.com> In-Reply-To: <45D0DDED.1070400@ca.com> 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=0.144 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/119 X-Sequence-Number: 23173 Brian Cox wrote: > > There are 1.9M rows in ts_defects and indexes on b.ts_id (primary key) > d.ts_biz_event_id and d.ts_occur_date. Both queries below return 0 > rows. The 1st runs fast and the 2nd > 400x slower. The 2nd query > differs from the 1st only by the addition of "limit 1". > > Why the big difference in performance? Please run EXPLAIN ANALYZE on both queries, and send back the results. Also, what indexes are there on the tables involved? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com