Simple but slow - Mailing list pgsql-novice
| From | Chad Thompson |
|---|---|
| Subject | Simple but slow |
| Date | |
| Msg-id | 006401c24898$680a2780$32021aac@chad Whole thread Raw |
| Responses |
Re: Simple but slow
|
| List | 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'
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
-> 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
pgsql-novice by date: