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
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