On Dec 23, 2010, at 12:52 PM, Tom Lane wrote: > Ben <midfield@xxxxxxxxx> writes: >> i have a schema similar to the following > >> 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; > > 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. > > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
