INET datatype and '<<' operator results in bad row estimate - Mailing list pgsql-bugs

Postgres version: 9.3.4

Note: This behavior appears to have been fixed in 9.4-beta1.  I am
reporting it in case it can be fixed in 9.3.x.  Perhaps it should be
mentioned in the 9.4.x release notes (or did I miss it?)

A simple query of the form:

mike=# explain select * from swmon_internal.arpmon a join swmon_internal.macaddr_ip mi on( a.macaddr_ip = mi.id )
whereip << '128.175.10.0/25'; 
                                                     QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=5215.15..112839.99 rows=1591378 width=53)
    Hash Cond: (a.macaddr_ip = mi.id)
    ->  Seq Scan on arpmon a  (cost=0.00..59883.53 rows=3182753 width=32)
    ->  Hash  (cost=8.45..8.45 rows=416536 width=21)
          ->  Index Scan using macaddr_ip_ip_macaddr_index on macaddr_ip mi  (cost=0.42..8.45 rows=416536 width=21)
                Index Cond: ((ip > '128.175.10.0/25'::inet) AND (ip <= '128.175.10.127'::inet))
                Filter: (ip << '128.175.10.0/25'::inet)

Results in a bad row guess (416536) and hence a slower plan is
chosen since postgres is expecting far too many rows.  The number
chosen is usually 1/2 the number of rows in the macaddr_ip table, by
the way.

Notice that if I manually add the index condition to the query:

mike=# explain select * from swmon_internal.arpmon a join swmon_internal.macaddr_ip mi on( a.macaddr_ip = mi.id )
whereip << '128.175.10.0/25' and ((ip > '128.175.10.0/25'::inet) AND (ip <= '128.175.10.127'::inet)); 
                                                                         QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.85..97.81 rows=4 width=53)
    ->  Index Scan using macaddr_ip_ip_macaddr_index on macaddr_ip mi  (cost=0.42..8.45 rows=1 width=21)
          Index Cond: ((ip > '128.175.10.0/25'::inet) AND (ip <= '128.175.10.127'::inet) AND (ip >
'128.175.10.0/25'::inet)AND (ip <= '128.175.10.127'::inet)) 
          Filter: (ip << '128.175.10.0/25'::inet)
    ->  Index Scan using arpmon_macaddr_ip_index on arpmon a  (cost=0.43..89.13 rows=22 width=32)
          Index Cond: (macaddr_ip = mi.id)

The number of estimated rows from macaddr_ip is about right (22) and a
much better plan is used.

vacuum analyze has been done and the histogram for the table is reasonable.

mike=# \d swmon_internal.macaddr_ip
                             Table "swmon_internal.macaddr_ip"
  Column  |  Type   |                              Modifiers
---------+---------+---------------------------------------------------------------------
  id      | bigint  | not null default nextval('swmon_internal.macaddr_ip_seq'::regclass)
  macaddr | macaddr | not null
  ip      | inet    | not null
Indexes:
     "macaddr_ip_id_key" UNIQUE CONSTRAINT, btree (id)
     "macaddr_ip_ip_macaddr_index" UNIQUE, btree (ip, macaddr)
     "macaddr_ip_host_ip_index" btree (host(ip))
     "macaddr_ip_macaddr_index" btree (macaddr)
Referenced by:
     TABLE "swmon_internal.arpmon" CONSTRAINT "arpmon_macaddr_ip_fkey" FOREIGN KEY (macaddr_ip) REFERENCES
swmon_internal.macaddr_ip(id)ON UPDATE CASCADE 

mike=# \d swmon_internal.arpmon
                                       Table "swmon_internal.arpmon"
    Column   |           Type           |                            Modifiers
------------+--------------------------+-----------------------------------------------------------------
  id         | bigint                   | not null default nextval('swmon_internal.arpmon_seq'::regclass)
  macaddr_ip | bigint                   | not null
  start      | timestamp with time zone | not null default now()
  finish     | timestamp with time zone | not null default now()
Indexes:
     "arpmon_id_key" UNIQUE CONSTRAINT, btree (id)
     "arpmon_finish_index" btree (finish)
     "arpmon_macaddr_ip_index" btree (macaddr_ip)
     "arpmon_start_index" btree (start)
Foreign-key constraints:
     "arpmon_macaddr_ip_fkey" FOREIGN KEY (macaddr_ip) REFERENCES swmon_internal.macaddr_ip(id) ON UPDATE CASCADE
Referenced by:
     TABLE "swmon_internal.link_swmon_arpmon" CONSTRAINT "link_swmon_arpmon_arpmon_id_fkey" FOREIGN KEY (arpmon_id)
REFERENCESswmon_internal.arpmon(id) ON UPDATE CASCADE 

Again, this works correctly in 9.4-beta1.

Thanks,

Mike

-
Mike Porter
PGP Fingerprint: F4 AE E1 9F 67 F7 DA EA  2F D2 37 F3 99 ED D1 C2