Обсуждение: Poor performance in inet << cidr join

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

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

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

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