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

From Matthew
Subject Re: Severe performance problems for simple query
Date
Msg-id Pine.LNX.4.64.0804071723490.20402@aragorn.flymine.org
Whole thread Raw
In response to Severe performance problems for simple query  (Dimi Paun <dimi@lattica.com>)
Responses Re: Severe performance problems for simple query
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)

pgsql-performance by date:

Previous
From: Matthew
Date:
Subject: Re: Severe performance problems for simple query
Next
From: Heikki Linnakangas
Date:
Subject: Re: Severe performance problems for simple query