pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Litao Wu <litaowu@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: network address query
Date: Thu, 1 Jul 2004 09:12:47 -0700 (PDT)
Message-ID: <20040701161247.98527.qmail@web13122.mail.yahoo.com> (raw)
In-Reply-To: <13105.1088623982@sss.pgh.pa.us>

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 



Message-ID: <20040701161247.98527.qmail@web13122.mail.yahoo.com>
Permalink:  ../20040701161247.98527.qmail@web13122.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040701161247.98527.qmail@web13122.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: litaowu@yahoo.com
  Subject: Re: network address query
  In-Reply-To: <20040701161247.98527.qmail@web13122.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox