From: | "Andreas Karlsson" <andreas(at)proxel(dot)se> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5705: btree_gist: Index on inet changes query result |
Date: | 2010-10-11 20:55:35 |
Message-ID: | 201010112055.o9BKtZf7011251@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5705
Logged by: Andreas Karlsson
Email address: andreas(at)proxel(dot)se
PostgreSQL version: 9.1
Operating system: Linux
Description: btree_gist: Index on inet changes query result
Details:
Hi,
I was looking at the code to see how one would improve indexing of the inet
types and saw an inconsistency between the compressed format
(gbt_inet_compress) and how network_cmp_internal works. The btree_gist
module ignores the netmask.
This means that while the operator thinks 1.255.255.200/8 is smaller than
1.0.0.0 the GiST index thinks the opposite.
An example for how to reproduce the bug:
-- Demostrate that I did not get the operator wrong. :)
SELECT '1.255.255.200/8'::inet < '1.0.0.0'::inet;
?column?
----------
t
(1 row)
-- Create and populate table
CREATE TABLE inet_test (a inet);
INSERT INTO inet_test VALUES ('1.255.255.200/8');
-- Without index
SELECT * FROM inet_test WHERE a < '1.0.0.0'::inet;
a
-----------------
1.255.255.200/8
(1 row)
EXPLAIN SELECT * FROM inet_test WHERE a < '1.0.0.0'::inet;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on inet_test (cost=0.00..26.38 rows=437 width=32)
Filter: (a < '1.0.0.0'::inet)
(2 rows)
-- With index
CREATE INDEX inet_test_idx ON inet_test USING gist (a);
SET enable_seqscan = false;
SELECT * FROM inet_test WHERE a < '1.0.0.0'::inet;
a
---
(0 rows)
EXPLAIN SELECT * FROM inet_test WHERE a < '1.0.0.0'::inet;
QUERY PLAN
----------------------------------------------------------------------------
----
Index Scan using inet_test_idx on inet_test (cost=0.00..8.27 rows=1
width=32)
Index Cond: (a < '1.0.0.0'::inet)
(2 rows)
-- With btree index
DROP INDEX inet_test_idx;
CREATE INDEX inet_test_btree_idx ON inet_test USING btree (a);
SELECT * FROM inet_test WHERE a < '1.0.0.0'::inet;
a
-----------------
1.255.255.200/8
(1 row)
EXPLAIN SELECT * FROM inet_test WHERE a < '1.0.0.0'::inet;
QUERY PLAN
----------------------------------------------------------------------------
----
Index Scan using inet_test_btree_idx on inet_test (cost=0.00..8.27 rows=1
width=32)
Index Cond: (a < '1.0.0.0'::inet)
(2 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-10-11 23:07:19 | Re: BUG #5703: Streaming replication: FATAL: bad buffer id: 0 |
Previous Message | Dave Page | 2010-10-11 20:34:00 | Re: BUG #5699: Postgres runs only under default user. |