pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jesse d <jesse_dv2001@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Help with slow running query
Date: Mon, 7 Mar 2005 15:54:08 -0800 (PST)
Message-ID: <20050307235408.30049.qmail@web42106.mail.yahoo.com> (raw)

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/



Message-ID: <20050307235408.30049.qmail@web42106.mail.yahoo.com>
Permalink:  ../20050307235408.30049.qmail@web42106.mail.yahoo.com/
Also on:    postgresql.org/message-id/20050307235408.30049.qmail@web42106.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jesse_dv2001@yahoo.com
  Subject: Re: Help with slow running query
  In-Reply-To: <20050307235408.30049.qmail@web42106.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox