"Cristian Prieto" <cristian(at)clickdiario(dot)com> writes:
> mydb=# explain analyze select locid from geoip_block where
> '126.96.36.199'::inet between start_block and end_block;
> As you see it still using a sequential scan in the table and ignores the
> index, any other suggestion?
That two-column index is entirely useless for this query; in fact btree
indexes of any sort are pretty useless. You really need some sort of
multidimensional index type like rtree or gist. There was discussion
just a week or three ago of how to optimize searches for intervals
overlapping a specified point, which is identical to your problem.
Can't remember if the question was about timestamp intervals or plain
intervals, but try checking the list archives.
regards, tom lane
In response to
pgsql-performance by date
|Next:||From: Don Isgitt||Date: 2005-09-26 19:44:42|
|Subject: Re: Index use in BETWEEN statement...|
|Previous:||From: Alvaro Herrera||Date: 2005-09-26 18:42:53|
|Subject: Re: int2 vs int4 in Postgres|
pgsql-general by date
|Next:||From: Tom Lane||Date: 2005-09-26 19:23:33|
|Subject: Re: Error migrating from 7.4.3 to 8.0.3 |
|Previous:||From: Peter Wiersig||Date: 2005-09-26 18:58:54|
|Subject: Re: SQL command to dump the contents of table failed: PQendcopy()|