Subject: Re: Severe performance problems for simple query
From: Dimi Paun <dimi@lattica.com>
To: Matthew <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <Pine.LNX.4.64.0804071723490.20402@aragorn.flymine.org>
References: <1207581719.5399.119.camel@dimi.lattica.com>
	<Pine.LNX.4.64.0804071723490.20402@aragorn.flymine.org>
Content-Type: text/plain
Organization: Lattica, Inc.
Date: Mon, 07 Apr 2008 12:45:42 -0400
Message-Id: <1207586742.5399.144.camel@dimi.lattica.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


On Mon, 2008-04-07 at 17:27 +0100, Matthew wrote:
> 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.

Thanks Matthew, this seems to be indeed a lot faster:

perpedes_db=# CREATE INDEX temp1 ON static.ipligenceipaddress (ipFrom);
CREATE INDEX
perpedes_db=# explain ANALYZE select * from static.ipligenceipaddress where ipfrom <= 2130706433 ORDER BY ipFrom DESC LIMIT 1;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.03 rows=1 width=145) (actual time=0.060..0.060 rows=1 loops=1)
   ->  Index Scan Backward using temp1 on ipligenceipaddress  (cost=0.00..83453.92 rows=2685155 width=145) (actual time=0.057..0.057 rows=1 loops=1)
         Index Cond: (ipfrom <= 2130706433)
 Total runtime: 0.094 ms
(4 rows)


However, it is rather disappointing that the DB can't figure out
how to execute such a simple query in a half decent manner (seq scan
on an indexed table for a BETWEEN filter doesn't qualify :)).

Many thanks!

-- 
Dimi Paun <dimi@lattica.com>
Lattica, Inc.