The following bug has been logged online: Bug reference: 5705 Logged by: Andreas Karlsson Email address: andr...@proxel.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) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs