Re: Index use in BETWEEN statement...

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Cristian Prieto" <cristian(at)clickdiario(dot)com>
Cc: "'Sean Davis'" <sdavis2(at)mail(dot)nih(dot)gov>, pgsql-general(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: Index use in BETWEEN statement...
Date: 2005-09-26 19:17:23
Message-ID: 1896.1127762243@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

"Cristian Prieto" <cristian(at)clickdiario(dot)com> writes:
> mydb=# explain analyze select locid from geoip_block where
> '216.230.158.50'::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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-09-26 19:23:33 Re: Error migrating from 7.4.3 to 8.0.3
Previous Message Peter Wiersig 2005-09-26 18:58:54 Re: SQL command to dump the contents of table failed: PQendcopy()

Browse pgsql-performance by date

  From Date Subject
Next Message Don Isgitt 2005-09-26 19:44:42 Re: Index use in BETWEEN statement...
Previous Message Alvaro Herrera 2005-09-26 18:42:53 Re: int2 vs int4 in Postgres