Simple query takes 5+ minutes

From: "Jesse D(dot)" <jesse_dv2001(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Simple query takes 5+ minutes
Date: 2005-03-05 03:32:25
Message-ID: 1109993545.459968.156480@l41g2000cwc.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hope this is the correct group.

I am running 8.0.1 on XP P4 2.6 1GB for dev work
and the following simple query takes 5+ minutes
to complete. Would like to get this down to <2-3 seconds.

Other RDBMS complete it in <1 second.

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

-- from http://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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Afton & Ray Still 2005-03-05 03:48:53 Re: PostgreSQL installation problem on Windows XP Home
Previous Message Tzahi Fadida 2005-03-05 01:46:53 Re: preserving data after updates