Teodor, would you please comment on this bug after reading the entire thread which includes comments from other developers?
http://archives.postgresql.org/pgsql-bugs/2010-10/msg00099.php Thanks. --------------------------------------------------------------------------- Andreas Karlsson wrote: > > 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 -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs