Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 125611337B49 for ; Thu, 23 Dec 2010 16:07:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 26376-07 for ; Thu, 23 Dec 2010 20:06:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f46.google.com (mail-yw0-f46.google.com [209.85.213.46]) by mail.postgresql.org (Postfix) with ESMTP id B4CDA13364DE for ; Thu, 23 Dec 2010 16:06:55 -0400 (AST) Received: by ywl5 with SMTP id 5so2762819ywl.19 for ; Thu, 23 Dec 2010 12:06:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:from:content-type :content-transfer-encoding:subject:date:message-id:to:mime-version :x-mailer; bh=N2ehyF/HyLjK+XZhuSFbqDFdwBZ9xGeB3t/9LabH5Oo=; b=a3MKzILkAsL7W+emVmvvzxuY/0NkVntYBZh4g/ZKeXNFV9QUtrS/avfrBLFbjJ1qCr C5yEMu64J0lPAcZpfkhk3Id+3XzM9WbwgeRjgdlvwWL2Ux5ytRDHzQvTS50VH7X3+eld gggJqUCSrNnJ8UgH6LPS805B5BRNVNTtYBylk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=from:content-type:content-transfer-encoding:subject:date:message-id :to:mime-version:x-mailer; b=qzgIyPswt20jfx6DXbfwgeBcNQEYnSjx4fk59T7K+L8k2+lhmK1ghBidfZn+AfHOEr 1h2gQOMpMx/Y7Qh+UDHYbm6pDMKweZ1WhLwg3SgZy0jqDnNVPLu9dMKCSfe2Jxyw38C9 HzISVUXWe7hil3qPfeSyp7tldNrXqrGBYAlI4= Received: by 10.236.103.145 with SMTP id f17mr16708144yhg.61.1293134805353; Thu, 23 Dec 2010 12:06:45 -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 a46sm4555559yhd.43.2010.12.23.12.06.43 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 23 Dec 2010 12:06:43 -0800 (PST) From: Ben Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Subject: encourging bitmap AND Date: Thu, 23 Dec 2010 12:06:40 -0800 Message-Id: <5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com> To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Apple Message framework v1082) X-Mailer: Apple Mail (2.1082) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.589 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201012/300 X-Sequence-Number: 41752 hello -- i have a schema similar to the following create table foo ( id integer not null, val integer not null, s integer not null, e integer not null ); create index foo_s_idx on foo using btree (s); create index foo_e_idx on foo using btree (e); i want to do queries like select * from foo where 150 between s and e; this usually gives me index or bitmap scans on one of the indices, plus = a filter for the other condition. this is not terribly efficient as the = table is large (billions of rows), but there should only be a few = thousand rows with s < k < e for any k. the data is id, value, interval = (s, e), with s < e, and e - s is "small". i am experimenting and would like to see the effect of using a bitmap = index "AND" scan using both indices. as far as i can tell, there are no = easy ways to force or encourage this -- there are no switches like = enable_seqscan and such which force the use of bitmap AND, and i don't = know how to tell the query planner about the structure of the data (i = don't think this is adequately captured in any of the statistics it = generates, i would need multi-column statistics.) any clues? best regards, ben=