Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 61E8B6336E2 for ; Mon, 11 Jan 2010 18:15:50 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64582-04 for ; Mon, 11 Jan 2010 22:15:39 +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.152]) by mail.postgresql.org (Postfix) with ESMTP id A1C64633673 for ; Mon, 11 Jan 2010 18:15:39 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 19so9108236fgg.1 for ; Mon, 11 Jan 2010 14:15:37 -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=vWec6JBUz2EChvJ/0mx9QhOPINbU7j9ZZq25ur94HIU=; b=jla6gd/kVX6IFD/jJVT2qgRoCTCP3BahRdQ6npROqYU4cRoJCCmNaxoZDxYog0E3bV chxIc8K56cJpDxnaW6ZHqZqxtoZspJnEHGZkDCX7bYjcRZoKg/IFE2nc+PxYEd11+lTm Ldcoz2iSApEb9u74BEovSjn/H//dEi2juTJSk= 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=THEv5PIxl5tLNMPhxZF751eG2HQMo9tpr3AkmCIEfwc5IdYYqnmYkbQ1FmxJ29EsVM Yj2sJ0PAZzBqsYhI3weYt/942dzQ3tMinyFfoW0azWIsD4b5ZyIXZPMOkLKFERqfim6v IYAaMUVaTZ9jT8bL6/kfmcOmqxMlA/cYQwIao= MIME-Version: 1.0 Received: by 10.239.179.89 with SMTP id c25mr1220545hbg.156.1263248137811; Mon, 11 Jan 2010 14:15:37 -0800 (PST) In-Reply-To: <4B4B7ED4.8070208@wizmail.org> References: <4B49DE7F.8080808@wizmail.org> <603c8f071001101853p6dd9517bofb7fcef43c97d79e@mail.gmail.com> <4B4B7ED4.8070208@wizmail.org> Date: Mon, 11 Jan 2010 17:15:37 -0500 Message-ID: <603c8f071001111415q11e5731dm3bfee4f1f4926919@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=0.589 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188 X-Spam-Level: X-Archive-Number: 201001/226 X-Sequence-Number: 37106 On Mon, Jan 11, 2010 at 2:41 PM, Jeremy Harris wrote: > On 01/11/2010 02:53 AM, Robert Haas wrote: >> >> On Sun, Jan 10, 2010 at 9:04 AM, Jeremy Harris =A0wrote= : >>> >>> Needing to use an external (on-disk) sort method, when taking >>> only 90MB, looks odd. > > [...] >> >> Well, you'd need to have work_mem> =A090 MB for that not to happen, and >> very few people can afford to set that setting that high. =A0If 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... > > Oh. =A0That's, um, a real pity given the cost of going external. =A0Any h= ope > of a more dynamic allocation of memory resource in the future? > Within a single query plan, the number of sorts is known; how about > a sort-mem limit per query rather than per sort (as a first step)? Unfortunately, it's not the case that the number of sorts is known - the amount of memory available to the sort affects its cost, so if we knew we were going to have more or less memory it would affect whether we chose the plan involving the sort in the first place. My previous musings on this topic are here: http://archives.postgresql.org/pgsql-hackers/2009-10/msg00125.php ...Robert