pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ben <midfield@gmail.com>
To: pgsql-performance@postgresql.org
Subject: encourging bitmap AND
Date: Thu, 23 Dec 2010 12:06:40 -0800
Message-ID: <5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com> (raw)

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


view thread (6+ messages)  latest in thread

Message-ID: <5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com>
Permalink:  ../5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com/
Also on:    postgresql.org/message-id/5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: midfield@gmail.com
  Subject: Re: encourging bitmap AND
  In-Reply-To: <5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com>
  References: <5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox