Re: Severe performance problems for simple query - Mailing list pgsql-performance

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 ipFrom, and LIMIT to the first result:

SELECT blah FROM table_name
   WHERE ipFrom <= 42 ORDER BY ipFrom DESC LIMIT 1

This should run *very* quickly. However, if any entries overlap at all
then you will get incorrect results.

Matthew

--
I'm always interested when [cold callers] try to flog conservatories.
Anyone who can actually attach a conservatory to a fourth floor flat
stands a marginally better than average chance of winning my custom.
(Seen on Usenet)