* Dimi Paun:
> * 4.9 million records in a table (IP address info)
You should use the ip4r type for that.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Mon, 7 Apr 2008, Heikki Linnakangas wrote:
In that case, a regular index on (ipFrom, ipTo) should work just fine, and
that's what he's got. Actually, an index on just ipFrom would probably work
just as well. The problem is that the planner doesn't know about that special
relationship between
On Mon, 2008-04-07 at 17:27 +0100, Matthew wrote:
> Oh yes, if you can guarantee that no two entries overlap at all, then
> there is a simpler way. Just create a B-tree index on ipFrom as usual,
> sort by ipFrom, and LIMIT to the first result:
>
> SELECT blah FROM table_name
>WHERE ipFrom <=
On Mon, 2008-04-07 at 17:32 +0100, Heikki Linnakangas wrote:
> If I understood the original post correctly, the ipFrom and ipTo
> columns actually split a single linear ip address space into
> non-overlapping chunks. Something like this:
>
> ipFrom ipTo
> 1 10
> 10 20
> 20 50
>
Matthew wrote:
On Mon, 7 Apr 2008, Dimi Paun wrote:
* bad performance on queries of the form:
select * from ipTable where ipFrom <= val and val <= ipTo
This type of query is very hard for a normal B-tree index to answer. For
example, say val is half-way between min and max values. If you
On Mon, 7 Apr 2008, Dimi Paun wrote:
* bad performance on queries of the form:
select * from ipTable where ipFrom <= val and val <= ipTo
Oh yes, if you can guarantee that no two entries overlap at all, then
there is a simpler way. Just create a B-tree index on ipFrom as usual,
sort by ip
On Mon, 7 Apr 2008, Dimi Paun wrote:
* bad performance on queries of the form:
select * from ipTable where ipFrom <= val and val <= ipTo
This type of query is very hard for a normal B-tree index to answer. For
example, say val is half-way between min and max values. If you have an
index
Hi folks,
Here is the executive summary:
* RHEL5 (postgresql 8.1, .conf tweaked for performance [1])
* 2x Intel E5410 @ 2.33GHz (8 cores), 8GB RAM, 15KRPM SAS disks
* 4.9 million records in a table (IP address info)
* composite primary key: primary key(ipFrom, ipTo)
* ipFrom/ipTo are int
