Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 656F71337B7E for ; Thu, 23 Dec 2010 17:02:08 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 54368-08 for ; Thu, 23 Dec 2010 21:02:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gw0-f46.google.com (mail-gw0-f46.google.com [74.125.83.46]) by mail.postgresql.org (Postfix) with ESMTP id EC9D113364DE for ; Thu, 23 Dec 2010 17:02:00 -0400 (AST) Received: by gwj20 with SMTP id 20so3693743gwj.19 for ; Thu, 23 Dec 2010 13:02:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:subject:mime-version :content-type:from:in-reply-to:date:cc:content-transfer-encoding :message-id:references:to:x-mailer; bh=yCTbULqWbStTNhXRr0OjzgditlpFm7A6o88yWefLAW8=; b=nmq/QP5b/+RdJY2igqoGce7nTwpiLXTtUsx99B+S3mxn+Io3O6rTTZwN8NU9MCxWSC TngWyU4MQ75i4xSdJOU/Pg0ysuxnn14tiis/ff07j9ThiAE5EGUwGao+2eJyXC8IRw42 YoflKyKUk8jp8b1Nj/oRttB/VYdDdeSKr7UNM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=subject:mime-version:content-type:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to:x-mailer; b=bF1c5FlA5PQ4i8D3Nb++x8spVKIAsGo6zpBNUOhVpb9/te2zeOKO+/h9BSECNvAV0k GvslhHnQkjFVOkZ3obsKeThJPpvoq3fpTFJaw0o4FElwgBQFhGcuzMkXmFwUk6nz5Trl i0wLnpY7uHoYXRMvsSTLuzb94S/HFD/9TW4Eo= Received: by 10.90.27.4 with SMTP id a4mr19252aga.135.1293138119096; Thu, 23 Dec 2010 13:01:59 -0800 (PST) Received: from [192.168.1.106] (74-95-195-236-SFBA.hfc.comcastbusiness.net [74.95.195.236]) by mx.google.com with ESMTPS id i10sm12901534anh.32.2010.12.23.13.01.57 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 23 Dec 2010 13:01:58 -0800 (PST) Subject: Re: encourging bitmap AND Mime-Version: 1.0 (Apple Message framework v1082) Content-Type: text/plain; charset=us-ascii From: Ben In-Reply-To: <27812.1293137532@sss.pgh.pa.us> Date: Thu, 23 Dec 2010 13:01:48 -0800 Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <2879498D-E897-4FD0-8A73-52BF85B65D1C@gmail.com> References: <5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com> <27812.1293137532@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.1082) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201012/302 X-Sequence-Number: 41754 On Dec 23, 2010, at 12:52 PM, Tom Lane wrote: > Ben writes: >> i have a schema similar to the following >=20 >> create index foo_s_idx on foo using btree (s); >> create index foo_e_idx on foo using btree (e); >=20 >> i want to do queries like >=20 >> select * from foo where 150 between s and e; >=20 > That index structure is really entirely unsuited to what you want to = do, > so it's not surprising that the planner isn't impressed with the idea = of > a bitmap AND. >=20 > I'd suggest setting up something involving a gist index over an > interval-ish datatype. The PERIOD datatype that Jeff Davis is fooling > with would do what you want --- see > http://pgfoundry.org/projects/temporal > http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/ > If you don't want any add-on parts involved, you could fake it by = using > a box or possibly lseg. Thanks for the quick response. I've already played a lot with the = PERIOD datatype and GIST, it works pretty good, but I found that the = lack of statistics and real selectivity functions hurt me. I was = experimenting with the two column setup as an alternative, but if you = think this is a dead end I'll look elsewhere. Best regards, Ben=