From: Dimi Paun <dimi@lattica.com>
To: Matthew <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Severe performance problems for simple query
Date: Mon, 07 Apr 2008 12:45:42 -0400
Message-ID: <1207586742.5399.144.camel@dimi.lattica.com> (raw)
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>
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.
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, matthew@flymine.org
Subject: Re: Severe performance problems for simple query
In-Reply-To: <1207586742.5399.144.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