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 16583D95AD
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Nov 2005 18:07:45 -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 15903-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Nov 2005 22:07:42 +0000 (GMT)
Received: from mail1.chaos1.de (Mail1.Chaos1.DE [213.160.12.53])
	by svr1.postgresql.org (Postfix) with ESMTP id C86F2D9595
	for <pgsql-sql@postgresql.org>; Wed,  2 Nov 2005 18:07:41 -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 1EXQli-0004sE-3b
	for pgsql-sql@postgresql.org; Wed, 02 Nov 2005 23:07:42 +0100
Mime-Version: 1.0 (Apple Message framework v623)
In-Reply-To: <a366b1ecc7f4d6f4b139106cf524f2e7@Chaos1.DE>
References: <a366b1ecc7f4d6f4b139106cf524f2e7@Chaos1.DE>
Content-Type: multipart/alternative; boundary=Apple-Mail-10-190720834
Message-Id: <60328928649b77f3e95a407d47b400eb@Chaos1.DE>
From: Axel Rau <Axel.Rau@Chaos1.DE>
Subject: Re: Poor performance in inet << cidr join (Resolved)
Date: Wed, 2 Nov 2005 23:07:39 +0100
To: pgsql-sql@postgresql.org
X-Mailer: Apple Mail (2.623)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.04 required=5 tests=[AWL=0.040]
X-Spam-Score: 0.04
X-Spam-Level: 
X-Archive-Number: 200511/35
X-Sequence-Number: 23287


--Apple-Mail-10-190720834
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	delsp=yes;
	format=flowed


Am 31.10.2005 um 19:53 schrieb Axel Rau:

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

rtree_inet from John Hansen does the trick:
	http://archives.postgresql.org/pgsql-hackers/2005-01/msg01038.php
First test shows query time of 85 instead of 2745 seconds:
                                            QUERY PLAN
------------------------------------------------------------------------ 
-------------------------
  Aggregate  (cost=1768557.75..1768557.75 rows=1 width=0)
    ->  Seq Scan on event e  (cost=0.00..1768040.83 rows=206769 width=0)
          Filter: (NOT (subplan))
          SubPlan
            ->  Index Scan using network_id_rtree on network n   
(cost=0.00..21.28 rows=5 width=0)
                  Index Cond: ($0 &<< (id)::inet)

Question: Can rtree_inet be included in the core?
Axel

Axel Rau, Frankfurt, Germany                           +49-69-951418-0

--Apple-Mail-10-190720834
Content-Transfer-Encoding: 7bit
Content-Type: text/enriched;
	charset=US-ASCII



Am 31.10.2005 um 19:53 schrieb Axel Rau:


<excerpt><fontfamily><param>Courier</param>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?

</fontfamily></excerpt><fontfamily><param>Courier</param>

rtree_inet from John Hansen does the trick:

	http://archives.postgresql.org/pgsql-hackers/2005-01/msg01038.php

First test shows query time of 85 instead of 2745 seconds:

                                           QUERY PLAN                                            

-------------------------------------------------------------------------------------------------

 Aggregate  (cost=1768557.75..1768557.75 rows=1 width=0)

   ->  Seq Scan on event e  (cost=0.00..1768040.83 rows=206769 width=0)

         Filter: (NOT (subplan))

         SubPlan

           ->  Index Scan using network_id_rtree on network n 
(cost=0.00..21.28 rows=5 width=0)

                 Index Cond: ($0 &<<<< (id)::inet)


</fontfamily>Question: Can
<fontfamily><param>Courier</param>rtree_inet be included in the core?</fontfamily>

Axel


Axel Rau, Frankfurt, Germany                           +49-69-951418-0


--Apple-Mail-10-190720834--