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

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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

pgsql-performance by date

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

pgsql-general by date

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

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