pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Ben <midfield@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: encourging bitmap AND
Date: Thu, 23 Dec 2010 13:01:48 -0800
Message-ID: <2879498D-E897-4FD0-8A73-52BF85B65D1C@gmail.com> (raw)
In-Reply-To: <27812.1293137532@sss.pgh.pa.us>
References: <5297F6A0-AB39-49F1-80BB-8EE60F3E243E@gmail.com>
	<27812.1293137532@sss.pgh.pa.us>


On Dec 23, 2010, at 12:52 PM, Tom Lane wrote:

> Ben <midfield@gmail.com> 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

view thread (6+ messages)  latest in thread

Message-ID: <2879498D-E897-4FD0-8A73-52BF85B65D1C@gmail.com>
Permalink:  ../2879498D-E897-4FD0-8A73-52BF85B65D1C@gmail.com/
Also on:    postgresql.org/message-id/2879498D-E897-4FD0-8A73-52BF85B65D1C@gmail.com

copy link · copy postgr.es

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: midfield@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: encourging bitmap AND
  In-Reply-To: <2879498D-E897-4FD0-8A73-52BF85B65D1C@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