From: Axel Rau <Axel.Rau@Chaos1.DE>
To: pgsql-sql@postgresql.org
Subject: Poor performance in inet << cidr join
Date: Mon, 31 Oct 2005 19:53:57 +0100
Message-ID: <a366b1ecc7f4d6f4b139106cf524f2e7@Chaos1.DE> (raw)
Hi SQLers,
in 8.0, joining 2 tables:
CREATE TABLE network (
id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address'
...
)
CREATE TABLE address (
id inet PRIMARY KEY , -- 'PK of IPv4/6 host address'
...
)
as:
SELECT COUNT(*) FROM address A
WHERE NOT EXISTS (
SELECT A.id FROM network N WHERE A.id << N.id );
shows this query plan:
Aggregate (cost=2264.51..2264.51 rows=1 width=0)
-> Seq Scan on address a (cost=0.00..2225.86 rows=15459 width=0)
Filter: (NOT (subplan))
SubPlan
-> Seq Scan on network n (cost=0.00..107.95 rows=2038
width=0)
Filter: ($0 << (id)::inet)
The planner does not use the pk-indices. Poking around,
I could not find an operator class, which supports the containment
('<<') operator.
Is my conclusion correct?
How can the functionality be extended?
Is there any implementation available?
Thanks, Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
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-sql@postgresql.org
Cc: Axel.Rau@Chaos1.DE
Subject: Re: Poor performance in inet << cidr join
In-Reply-To: <a366b1ecc7f4d6f4b139106cf524f2e7@Chaos1.DE>
* 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