Thread

1. Poor performance in inet << cidr join

   Axel Rau <axel.rau@chaos1.de> — 2005-10-31T18:53:57Z

   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

2. Re: Poor performance in inet << cidr join (Resolved)

   Axel Rau <axel.rau@chaos1.de> — 2005-11-02T22:07:39Z

   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
   

3. Re: Poor performance in inet << cidr join (Resolved)

   Tom Lane <tgl@sss.pgh.pa.us> — 2005-11-02T23:22:14Z

   Axel Rau <Axel.Rau@Chaos1.DE> writes:
   > Question: Can rtree_inet be included in the core?
   
   No, because rtree is going away in 8.2.  Feel like converting that
   code to be a GIST opclass, instead?
   
   			regards, tom lane
   
   

4. Re: Poor performance in inet << cidr join (Resolved)

   Axel Rau <axel.rau@chaos1.de> — 2005-11-03T12:54:36Z

   -----BEGIN PGP SIGNED MESSAGE-----
   Hash: SHA1
   
   
   Am 03.11.2005 um 00:22 schrieb Tom Lane:
   
   > Axel Rau <Axel.Rau@Chaos1.DE> writes:
   >> Question: Can rtree_inet be included in the core?
   >
   > No, because rtree is going away in 8.2.  Feel like converting that
   > code to be a GIST opclass, instead?
   Perhaps. It would be a big step for me, since I started with pg 3 month 
   ago.
   As starting points, I found
   - - chapter 48 in the 8.0 manual
   - - rtree_gist in 8.0 contrib
   - - btree_gist in 8.1 contrib
   
   Any more?
   Axel
   Axel Rau, Frankfurt, Germany                           +49-69-951418-0
   -----BEGIN PGP SIGNATURE-----
   Version: GnuPG v1.2.4 (Darwin)
   
   iQEVAwUBQ2oIlMFz9+6bacTRAQIwHQgAgOKK5rxY4aTrEStJeljORZwUWQre66ZD
   ZoD6HYcVxJepRC9lEbakxLmdokHtaMp1rqWziiv7idlDqApc6deVlo7ESozFC2jZ
   Yb/hVBIxmaBuHFj2n/AbYwGPR18g1SLODhyOj6/QlciKLw0apAVLUb0iHCfS7ie0
   qSaCh/oARM8066SpMtdBX5oKLRgcgXYYtK9UNmX0njLqyDmCd9WZRrOwnLRRzptZ
   k5R1iMDrksV/Hifx9RcaGNRkQ4JZ15rf3OCLHgwMMwrpfLB0jMcBN8o1YEpZimJc
   Ffc5ChGlTfb4ADcCMGl3mBgBFNRoUZqMKrFakvEHJY65jz8ng3bl3w==
   =Hco5
   -----END PGP SIGNATURE-----