X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 708FE56539 for ; Mon, 7 Mar 2005 23:54:10 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49066-02 for ; Mon, 7 Mar 2005 23:54:07 +0000 (GMT) Received: from web42106.mail.yahoo.com (web42106.mail.yahoo.com [66.218.93.199]) by svr1.postgresql.org (Postfix) with SMTP id 91D1556535 for ; Mon, 7 Mar 2005 23:54:05 +0000 (GMT) Received: (qmail 30051 invoked by uid 60001); 7 Mar 2005 23:54:08 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=uNEpwc6gkcBZknkBcRBZzvvuvYPjdySyKbzPHvJI8m/DF9aF1k2wJuOlqzV76WYx5gXKhjTlDplmPJ9Cl5gEMdlRRf+SmsxnErOjFYb3I6e3nLbEZmqXpPsLadxXx8rFHlMJSHNdDeiKJl/ziebdCJxmMIsbqrX8K8jDHgyRcUc= ; Message-ID: <20050307235408.30049.qmail@web42106.mail.yahoo.com> Received: from [64.40.43.71] by web42106.mail.yahoo.com via HTTP; Mon, 07 Mar 2005 15:54:08 PST Date: Mon, 7 Mar 2005 15:54:08 -0800 (PST) From: jesse d Subject: Help with slow running query To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.251 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, FROM_ENDS_IN_NUMS, FROM_HAS_ULINE_NUMS X-Spam-Level: * X-Archive-Number: 200503/115 X-Sequence-Number: 10820 The following query takes approx. 3-5+ minutes to complete. I would like to get this down to around 2-3 seconds. Other RDBMS complete it in <1 second. I am running 8.0.1 on XP P4 2.6 1GB for dev work. select i.internalid, c.code from local.internal i inner join country.ip c on (i.ip between c.startip and c.endip) Nested Loop (cost=167.59..7135187.85 rows=31701997 width=10) (actual time=63.000..776094.000 rows=5235 loops=1) Join Filter: ((inner.ip >= outer.startip) AND (inner.ip <= outer.endip)) -> Seq Scan on ip c (cost=0.00..2071.02 rows=54502 width=28) (actual time=0.000..313.000 rows=54502 loops=1) -> Materialize (cost=167.59..219.94 rows=5235 width=15) (actual time=0.000..2.973 rows=5235 loops=54502) -> Seq Scan on internal i (cost=0.00..162.35 rows=5235 width=15) (actual time=0.000..16.000 rows=5235 loops=1) Total runtime: 776110.000 ms -- data from ip-to-country.webhosting.info CREATE TABLE country.ip -- 54,502 rows ( startip inet NOT NULL, endip inet NOT NULL, code char(2) NOT NULL, CONSTRAINT ip_pkey PRIMARY KEY (startip, endip) ); -- 1, 192.168.1.10, 192.168.2.100, US -- 2, 192.168.3.0, 192.168.3.118, US CREATE TABLE local.internal -- 5000+ rows ( internalid serial NOT NULL, ip inet NOT NULL, port int2 NOT NULL, CONSTRAINT internal_pkey PRIMARY KEY (internalid) ); CREATE INDEX ip_idx ON local.internal (ip); -- 1, 10.0.0.100, 80 -- 2, 10.0.0.102, 80 -- 3, 10.0.0.103, 443 -- postgresql.conf have tried many settings with no improvement max_connections = 50 shared_buffers = 30000 work_mem = 2048 sort_mem = 2048 Have tried many different indexes with no help: CREATE INDEX endip_idx ON country.ip; CREATE INDEX startip_idx ON country.ip; CREATE UNIQUE INDEX e_s_idx ON country.ip (endip, startip); Any suggestions would be greatly appreciated. __________________________________ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/