Re: Severe performance problems for simple query

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(at)lattica(dot)com>
Lattica, Inc.