encourging bitmap AND

From: Ben <midfield(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: encourging bitmap AND
Date: 2010-12-23 20:06:40
Message-ID: 5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-12-23 20:52:12 Re: encourging bitmap AND
Previous Message Andy 2010-12-23 19:58:18 Re: concurrent IO in postgres?