X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6707BD1B98D for ; Mon, 23 Feb 2004 11:48:10 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 44875-10 for ; Mon, 23 Feb 2004 07:48:10 -0400 (AST) Received: from expasy-ng.isb-sib.ch (expasy-f.unige.ch [192.33.215.142]) by svr1.postgresql.org (Postfix) with ESMTP id 502D8D1B9E5 for ; Mon, 23 Feb 2004 07:48:06 -0400 (AST) Received: from caliente (router.isb-sib.ch [192.33.215.254]) (authenticated bits=0) by expasy-ng.isb-sib.ch (8.12.10/8.12.8) with ESMTP id i1NBm3MD011016 for ; Mon, 23 Feb 2004 12:48:04 +0100 Message-ID: <000701c3fa02$e4e3d180$c300000a@caliente> From: "Eric Jain" To: "pgsql-performance" Subject: Slow join using network address function Date: Mon, 23 Feb 2004 12:48:02 +0100 Organization: Swiss Institute of Bioinformatics MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-sib-MailScanner-Information: Please contact the ISP for more information X-sib-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/261 X-Sequence-Number: 5785 I'm trying to join two tables on an inet column, where one of the columns may contain a subnet rather than a single host. Somehow the operation isn't completing quite fast enough, even though neither table is very large: table | rows --------------------+-------- clients | 115472 clients_commercial | 11670 First attempt, cancelled after running for half an hour: SELECT c.address AS address, cc.address AS network FROM clients c JOIN clients_commercial cc ON (c.address <<= cc.address) ; Nested Loop (cost=189.00..27359887.76 rows=607947200 width=22) Join Filter: ("outer".address <<= "inner".address) -> Seq Scan on clients c (cost=0.00..2074.76 rows=102176 width=11) -> Materialize (cost=189.00..308.00 rows=11900 width=11) -> Seq Scan on clients_commercial cc (cost=0.00..189.00 rows=11900 width=11) Second attempt, completes within 10 min: SELECT c.address AS address, cc.address AS network FROM clients c, clients_commercial cc WHERE c.commercial IS NULL AND c.address <<= cc.address ; Nested Loop (cost=189.00..139084.01 rows=3040450 width=22) Join Filter: ("outer".address <<= "inner".address) -> Seq Scan on clients c (cost=0.00..2074.76 rows=511 width=11) Filter: (commercial IS NULL) -> Materialize (cost=189.00..308.00 rows=11900 width=11) -> Seq Scan on clients_commercial cc (cost=0.00..189.00 rows=11900 width=11) Third attempt; provided some indexes, which unfortunately don't get used, making the query twice as slow as the previous one: SELECT c.address AS address, cc.address AS network FROM clients c, clients_commercial cc WHERE c.commercial IS NULL AND set_masklen(c.address, masklen(cc.address)) = cc.address ; CREATE INDEX clients_commercial_masklen_idx ON clients_commercial((masklen(address))); CREATE INDEX clients_32_idx ON clients((set_masklen(address, 32))); CREATE INDEX clients_24_idx ON clients((set_masklen(address, 24))); CREATE INDEX clients_16_idx ON clients((set_masklen(address, 16))); Nested Loop (cost=189.00..169488.51 rows=479 width=22) Join Filter: (set_masklen("outer".address, masklen("inner".address)) = "inner".address) -> Seq Scan on clients c (cost=0.00..2074.76 rows=511 width=11) Filter: (commercial IS NULL) -> Materialize (cost=189.00..308.00 rows=11900 width=11) -> Seq Scan on clients_commercial cc (cost=0.00..189.00 rows=11900 width=11) Anything else I could try? BTREE indexes don't seem to work with the <<= operator; is this not possible in principal, or simply something that has not been implmented yet?