Skip site navigation (1) Skip section navigation (2)

Re: Btree indexes, large numbers and <= comparisons

From: Toke Høiland-Jørgensen <toke(at)toke(dot)dk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Btree indexes, large numbers and <= comparisons
Date: 2007-03-29 20:52:16
Message-ID: 200703292252.17344.toke@toke.dk (view raw or flat)
Thread:
Lists: pgsql-general
> You can't usefully use a two-column btree index for this.  btree indexes
> are not magic, they're just ordered lists, and if you think about where
> the rows you want might fall in the sort order, you'll see that the two
> given constraints aren't helpful for constraining the indexscan: it'd
> have to scan every row up to range_start = 87654321, or every row after
> range_end = 87654321, depending on which is the first index column.
> (The btree lacks any way of using the fact that range_start <= range_end
> or that they're probably close together.)
>
> What you need is a different index type that's designed for this kind of
> query.  The closest thing available in the stock Postgres distribution
> is the contrib/seg module, which can handle overlap/intersection of line
> segments as an indexable query on a GIST index.  You'd store line
> segments representing your ranges in the index, and query using the
> "overlaps" operator.  However the seg data type is probably not
> immediately useful to you because it only stores float4 internally,
> and you seem to want more precision than that.  You'd need to make a
> modified flavor of seg that stores the endpoints with the same precision
> your range endpoint columns have.
>
> 			regards, tom lane

I'll look into it. Thank you :)

-Toke

In response to

pgsql-general by date

Next:From: Bryan MurphyDate: 2007-03-29 21:39:58
Subject: Deleted Flag/Unique Constraint
Previous:From: Ron JohnsonDate: 2007-03-29 20:25:09
Subject: Re: COPY command details

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group