From: Dimi Paun <dimi@lattica.com>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Matthew <matthew@flymine.org>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Severe performance problems for simple query
Date: Mon, 07 Apr 2008 12:41:25 -0400
Message-ID: <1207586485.5399.140.camel@dimi.lattica.com> (raw)
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dimi@lattica.com, heikki@enterprisedb.com, matthew@flymine.org
Subject: Re: Severe performance problems for simple query
In-Reply-To: <1207586485.5399.140.camel@dimi.lattica.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox