Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-10-11 23:07:19
Subject: Re: BUG #5703: Streaming replication: FATAL: bad buffer id: 0
Previous:From: Dave PageDate: 2010-10-11 20:34:00
Subject: Re: BUG #5699: Postgres runs only under default user.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group