X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 7004DDB59B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 31 Oct 2005 14:54:03 -0400 (AST)
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 53899-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 31 Oct 2005 18:54:01 +0000 (GMT)
Received: from mail1.chaos1.de (Mail1.Chaos1.de [213.160.12.53])
	by svr1.postgresql.org (Postfix) with ESMTP id 78881DB598
	for <pgsql-sql@postgresql.org>; Mon, 31 Oct 2005 14:54:00 -0400 (AST)
Received: from claudius6.in.chaos1.de ([192.168.220.106])
	by mail1.chaos1.de with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.44)
	id 1EWen9-0001Up-5j
	for pgsql-sql@postgresql.org; Mon, 31 Oct 2005 19:53:59 +0100
Mime-Version: 1.0 (Apple Message framework v623)
To: pgsql-sql@postgresql.org
Message-Id: <a366b1ecc7f4d6f4b139106cf524f2e7@Chaos1.DE>
Content-Type: multipart/alternative; boundary=Apple-Mail-53-6298864
From: Axel Rau <Axel.Rau@Chaos1.DE>
Subject: Poor performance in inet << cidr join
Date: Mon, 31 Oct 2005 19:53:57 +0100
X-Mailer: Apple Mail (2.623)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.043 required=5 tests=[AWL=0.043]
X-Spam-Score: 0.043
X-Spam-Level: 
X-Archive-Number: 200510/398
X-Sequence-Number: 23251


--Apple-Mail-53-6298864
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	format=flowed

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
--Apple-Mail-53-6298864
Content-Transfer-Encoding: 7bit
Content-Type: text/enriched;
	charset=US-ASCII

<fontfamily><param>Courier</param>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</fontfamily>
--Apple-Mail-53-6298864--