Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6DABD64FCC0 for ; Tue, 26 Aug 2008 14:00:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76880-10 for ; Tue, 26 Aug 2008 13:59:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f17.google.com (mail-gx0-f17.google.com [209.85.217.17]) by postgresql.org (Postfix) with ESMTP id 0AF0A6501D3 for ; Tue, 26 Aug 2008 13:59:51 -0300 (ADT) Received: by gxk10 with SMTP id 10so3452180gxk.19 for ; Tue, 26 Aug 2008 09:59:49 -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:sender :to:subject:cc:mime-version:content-type:content-transfer-encoding :content-disposition:x-google-sender-auth; bh=/Ipn+fQjphT9PUQvhKNJclv3xEEsdPrRLFzVIjbPjns=; b=j/U3GbwMgjRE8wrsXlYotRfiCPoByDSmi7Lgme1C68waQzk/nlbx6MK5QcZ+TPF054 ct5+hiYVFvSeyZfjQ81LNOkbNTUv7oo0bO59JbVT7nNLchlnjf3+o4g2bE0IjuWQjHXh C+eJ+LkigcXJzzS2fnNSXurSZ+vSCuy5GZ7h0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:sender:to:subject:cc:mime-version:content-type :content-transfer-encoding:content-disposition:x-google-sender-auth; b=GSaoO5JW8vVpq8ywVO7avwmTtaAClPSomIb3Y4dLj4TzHL5BGHDZq6Cdf2FuD9eNHT qXk03ByoAPWJ+XTmVOq9XAWpo7AlLFC/PsQeRLmpGCYvy/YBKvpSUH5yZT6lL6BRpjUW 1N9yG4VGw7q3SeULRz1U5/CVdhbb32pnZrj14= Received: by 10.150.51.6 with SMTP id y6mr9507471yby.239.1219769989836; Tue, 26 Aug 2008 09:59:49 -0700 (PDT) Received: by 10.151.45.8 with HTTP; Tue, 26 Aug 2008 09:59:49 -0700 (PDT) Message-ID: <7d10d2df0808260959g4284009bt90d028e23e68a015@mail.gmail.com> Date: Tue, 26 Aug 2008 17:59:49 +0100 From: "Frank Joerdens" To: pgsql-performance@postgresql.org Subject: Re: Query w empty result set with LIMIT orders of magnitude slower than without (SOLVED, pls disregard) Cc: Nic , "Seb Potter" MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Google-Sender-Auth: 5c32be50d597d2f6 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/334 X-Sequence-Number: 31215 Eh, there was a spurious join in that query which was created by an ORM which messed things up apparently. Sorry for the noise. This abstracted version of the original query that does the same is fast: woome=> EXPLAIN ANALYZE SELECT * FROM webapp_invite i INNER JOIN webapp_person p ON (i.id = p.id) WHERE p.is_suspended = false AND p.is_banned = false AND i.woouser = 'suggus' ORDER BY i.id DESC LIMIT 5; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=4549.51..4549.52 rows=5 width=238) (actual time=0.071..0.071 rows=0 loops=1) -> Sort (cost=4549.51..4549.58 rows=31 width=238) (actual time=0.070..0.070 rows=0 loops=1) Sort Key: i.id Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=12.20..4548.99 rows=31 width=238) (actual time=0.036..0.036 rows=0 loops=1) -> Bitmap Heap Scan on webapp_invite i (cost=12.20..1444.45 rows=382 width=44) (actual time=0.034..0.034 rows=0 loops=1) Recheck Cond: ((woouser)::text = 'suggus'::text) -> Bitmap Index Scan on webapp_invite_woouser_idx (cost=0.00..12.10 rows=382 width=0) (actual time=0.032..0.032 rows=0 loops=1) Index Cond: ((woouser)::text = 'suggus'::text) -> Index Scan using webapp_person_pkey on webapp_person p (cost=0.00..8.11 rows=1 width=194) (never executed) Index Cond: (p.id = i.id) Filter: ((NOT p.is_suspended) AND (NOT p.is_banned)) Total runtime: 0.183 ms (13 rows) Time: 1.114 ms