Poor performance in inet << cidr join

Поиск
Список
Период
Сортировка
От Axel Rau
Тема Poor performance in inet << cidr join
Дата
Msg-id a366b1ecc7f4d6f4b139106cf524f2e7@Chaos1.DE
обсуждение исходный текст
Ответы Re: Poor performance in inet << cidr join (Resolved)  (Axel Rau <Axel.Rau@Chaos1.DE>)
Список pgsql-sql
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

В списке pgsql-sql по дате отправления:

Предыдущее
От: Alessandro Busato
Дата:
Сообщение: pgSQL, executing generic query
Следующее
От: lucas@presserv.org
Дата:
Сообщение: Re: Fwd: Re: Referencing