Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E50856336E2 for ; Sun, 10 Jan 2010 22:53:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 71596-09 for ; Mon, 11 Jan 2010 02:53:33 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.153]) by mail.postgresql.org (Postfix) with ESMTP id 0D8F5633771 for ; Sun, 10 Jan 2010 22:53:41 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 19so8710475fgg.1 for ; Sun, 10 Jan 2010 18:53:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=+ynP+T97dldskxbVCO+ytKl0UQ22+2I08XzPah/0H1g=; b=xKG0jzPTPDR1IC0hgrcT1SyL+UXPlnw86ggQQjMVbxs4TW0Rzq7WAj/heTGCoFVZVZ 7nILZInEYasqW1fzSwUnwghpdctyzdnQejumfmHxIgufYC0SoY1aGHm1wkFGQfxrt4uX r/N9KLmMEFHeOpUo/LFjDG4mr9HTwmS06nmAk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=vECTBm78z2ETl13leriStPfOBmZrXouFsvIPQJdK7vEl9CJ1veJfH/MnmaVN+Ncqfe F3CSOAlpEWvyyD8D3if9U2Ey+iZMsWSzzC5CFeN/gtcY7ycSxtcDswW8m1uWNkyaoP3q kgGixFHEjkvSd/Ntj6tkENmBkWQmXMya5a7DI= MIME-Version: 1.0 Received: by 10.239.139.39 with SMTP id r39mr1449339hbr.116.1263178419331; Sun, 10 Jan 2010 18:53:39 -0800 (PST) In-Reply-To: <4B49DE7F.8080808@wizmail.org> References: <4B49DE7F.8080808@wizmail.org> Date: Sun, 10 Jan 2010 21:53:37 -0500 Message-ID: <603c8f071001101853p6dd9517bofb7fcef43c97d79e@mail.gmail.com> Subject: Re: Choice of bitmap scan over index scan From: Robert Haas To: Jeremy Harris Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.421 tagged_above=-10 required=5 tests=AWL=0.178, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201001/182 X-Sequence-Number: 37062 On Sun, Jan 10, 2010 at 9:04 AM, Jeremy Harris wrote: > On 01/10/2010 12:28 PM, Mathieu De Zutter wrote: >> >> Sort =A0(cost=3D481763.31..485634.61 rows=3D1548520 width=3D338) (actual >> time=3D5423.628..6286.148 rows=3D1551923 loops=3D1) >> =A0Sort Key: event_timestamp > > =A0> =A0Sort Method: =A0external merge =A0Disk: 90488kB >> >> =A0-> =A0Seq Scan on log_event =A0(cost=3D0.00..79085.92 rows=3D1548520 = width=3D338) >> (actual time=3D0.022..2195.527 rows=3D1551923 loops=3D1) >> =A0 =A0 =A0 =A0Filter: (event_timestamp > (now() - '1 year'::interval)) >> Total runtime: 6407.377 ms > > Needing to use an external (on-disk) sort method, when taking > only 90MB, looks odd. > > - Jeremy Well, you'd need to have work_mem > 90 MB for that not to happen, and very few people can afford to set that setting that high. If you have a query with three or four sorts using 90 MB a piece, and five or ten users running them, you can quickly kill the box... ...Robert