Simple but slow

From: "Chad Thompson" <chad(at)weblinkservices(dot)com>
To: "pgsql-novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Simple but slow
Date: 2002-08-20 22:24:52
Message-ID: 006401c24898$680a2780$32021aac@chad
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have this query that takes a very long time.
explain analyze select distinct on (l.full_phone) l.full_phone, l.id into temp temp_list_tz
from lists l, timezone tz
where l.area_code = tz.area_code
and l.client_id = 8
and tz.greenwich = '-5'
limit 100000;

these are the results
NOTICE: QUERY PLAN:

Limit (cost=96929.63..97822.45 rows=35713 width=36) (actual time=42477.82..43491.69 rows=100000 loops=1)
-> Unique (cost=96929.63..97822.46 rows=35713 width=36) (actual time=42477.82..43325.87 rows=100001 loops=1)
-> Sort (cost=96929.63..96929.63 rows=357132 width=36) (actual time=42477.80..42735.18 rows=102151 loops=1)
-> Hash Join (cost=9.59..41831.14 rows=357132 width=36) (actual time=25.29..16456.26 rows=352194 loops=1)
-> Seq Scan on lists l (cost=0.00..32881.18 rows=895244 width=29) (actual time=0.10..11342.50 rows=878098 loops=1)
-> Hash (cost=9.22..9.22 rows=148 width=7) (actual time=23.80..23.80 rows=0 loops=1)
-> Seq Scan on timezone tz (cost=0.00..9.22 rows=148 width=7) (actual time=21.72..23.45 rows=148 loops=1)
Total runtime: 46247.79 msec

I have about 2M records in lists, and about 400 in timezone, so it shouldnt be anything this serious.
I have indexed area_code in both tables, client_id in lists and greenwich in timezone
I have tried both group by and distinct on with negligable difference.

This doesnt seem like that complex a query. Am i doing something dumb again?

Here are the table structures.

-- Table: lists
CREATE TABLE "lists" (
"id" int8 DEFAULT nextval('"lists_id_seq"'::text) NOT NULL,
"first_name" varchar(50),
"middle_initial" char(1),
"last_name" varchar(30),
"address" varchar(40),
"city" varchar(30),
"state" char(2),
"zip" char(10),
"area_code" varchar(3),
"list_of_lists_id" int8,
CONSTRAINT "lists_pkey" PRIMARY KEY ("id"),
CONSTRAINT "area_code" FOREIGN KEY ("area_code") REFERENCES "timezone" ("area_code") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "client_id" FOREIGN KEY ("client_id") REFERENCES "clients" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "list_of_lists_id" FOREIGN KEY ("list_of_lists_id") REFERENCES "list_of_lists" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;

-- Table: timezone
CREATE TABLE "timezone" (
"id" int8 DEFAULT nextval('"timezone_id_seq"'::text) NOT NULL,
"area_code" varchar(3),
"state" varchar(2),
"full_state_name" varchar(30),
"GMT" int4,
"time_zone" varchar(10),
"daylight_savings" varchar(5),
"notes" text,
"greenwich" varchar(5),
"converted" int4,
CONSTRAINT "area_code_idx" UNIQUE ("area_code"),
CONSTRAINT "timezone_pkey" PRIMARY KEY ("id")
) WITH OIDS;

TIA
Chad

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Mark Stosberg 2002-08-21 02:15:37 Re: Event recurrence - in database or in application code ????
Previous Message Patrick Hatcher 2002-08-20 18:15:56 Re: importing dbase files daily