X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DDDA7D1B380 for ; Thu, 1 Jul 2004 13:12:59 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 19213-06 for ; Thu, 1 Jul 2004 16:12:50 +0000 (GMT) Received: from web13122.mail.yahoo.com (web13122.mail.yahoo.com [216.136.174.126]) by svr1.postgresql.org (Postfix) with SMTP id 0E273D1B46A for ; Thu, 1 Jul 2004 13:12:49 -0300 (ADT) Message-ID: <20040701161247.98527.qmail@web13122.mail.yahoo.com> Received: from [63.78.248.48] by web13122.mail.yahoo.com via HTTP; Thu, 01 Jul 2004 09:12:47 PDT Date: Thu, 1 Jul 2004 09:12:47 -0700 (PDT) From: Litao Wu Subject: network address query To: pgsql-performance@postgresql.org In-Reply-To: <13105.1088623982@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/2 X-Sequence-Number: 7393 Hi, I have query: explain SELECT * FROM ip_tracking T, ip_map C WHERE T.source_ip::inet >>= C.net; QUERY PLAN ------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..3894833367750.16 rows=51709297065144 width=111) Join Filter: ("outer".source_ip >>= ("inner".net)::inet) -> Seq Scan on ip_tracking t (cost=0.00..825050.68 rows=31093368 width=34) -> Seq Scan on ip_map c (cost=0.00..83686.66 rows=3326066 width=77) (4 rows) ip_tracking ( pk_col int, source_ip inet, .. the rest... ) There is one index ip_tracking_ip_idx btree (source_ip) ip_map ( net cidr, ... the rest...) Indexes: map_net_idx hash (net) If I change ">>=" to "=", the query plan is: QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..10798882243.63 rows=31093368 width=111) -> Seq Scan on ip_map c (cost=0.00..83686.66 rows=3326066 width=77) -> Index Scan using ip_tracking_ip_idx on ip_tracking t (cost=0.00..3236.72 rows=800 width=34) Index Cond: (t.source_ip = ("outer".net)::inet) (4 rows) This is my first time to deal network address type. Is it possible to make a query use index with operator of ">>=" like the above? Thanks, __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail