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


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 limit 1;
                                                                    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.524 rows=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 AND ipTo > 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=1 loops=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.