BUG #5705: btree_gist: Index on inet changes query result

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)

Responses

Browse pgsql-bugs by date

  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.