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)
|
Список | 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 по дате отправления: