Re: Severe performance problems for simple query

On Mon, 2008-04-07 at 17:32 +0100, Heikki Linnakangas wrote:
> If I understood the original post correctly, the ipFrom and ipTo
> columns actually split a single linear ip address space into
> non-overlapping  chunks. Something like this:
>
> ipFrom  ipTo
> 1       10
> 10      20
> 20      50
> 50      60
> ...
>

Indeed.

> In that case, a regular index on (ipFrom, ipTo) should work just fine,
> and that's what he's got. Actually, an index on just ipFrom would
> probably work just as well.

No, it doesn't:

perpedes_db=# CREATE INDEX temp1 ON static.ipligenceipaddress (ipFrom);
CREATE INDEX
perpedes_db=# explain ANALYZE select * from static.ipligenceipaddress where ipfrom <= 2130706433 and 2130706433 <= ipto
limit1; 
                                                                    QUERY PLAN
                          

--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.07 rows=1 width=145) (actual time=1519.526..1519.527 rows=1 loops=1)
   ->  Index Scan using temp1 on ipligenceipaddress  (cost=0.00..84796.50 rows=1209308 width=145) (actual
time=1519.524..1519.524rows=1 loops=1) 
         Index Cond: (ipfrom <= 2130706433)
         Filter: (2130706433 <= ipto)
 Total runtime: 1519.562 ms
(5 rows)

This is huge, I'd say...

> The problem is that the planner doesn't know  about that special
> relationship between ipFrom and ipTo. Perhaps it could be hinted by
> explicitly specifying "AND ipTo > ipFrom" in the query?

Unfortunately, it still does a seq scan:

perpedes_db=# SET enable_seqscan = on;
SET
perpedes_db=# explain ANALYZE select * from static.ipligenceipaddress where ipfrom <= 2130706433 and 2130706433 <= ipto
ANDipTo > ipFrom limit 1; 
                                                             QUERY PLAN
            

------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.35 rows=1 width=145) (actual time=1245.293..1245.294 rows=1 loops=1)
   ->  Seq Scan on ipligenceipaddress  (cost=0.00..142343.80 rows=403103 width=145) (actual time=1245.290..1245.290
rows=1loops=1) 
         Filter: ((ipfrom <= 2130706433) AND (2130706433 <= ipto) AND (ipto > ipfrom))
 Total runtime: 1245.335 ms
(4 rows)


> I don't know why the single index lookup took > 300ms, though. That
> does seem high to me.

That is my feeling. I would have expected order of magnitude faster
execution times, the DB runs on fairly decent hardware...

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