From: | Ron Arts <ron(dot)arts(at)neonova(dot)nl> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Index not used in join.. (example included) |
Date: | 2009-09-18 10:06:47 |
Message-ID: | 4AB35BB7.5050001@neonova.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi,
I don't understand why my query doesn't use the index on the name column:
CREATE TABLE celeb (
id serial,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);
create index celeb_name_index on celeb (name);
INSERT INTO celeb (name) values ('Cameron Diaz');
INSERT INTO celeb (name) values ('Angelina Jolie');
INSERT INTO celeb (name) values ('Reese Witherspoon');
CREATE TABLE detail (
id serial,
name varchar(255) NOT NULL,
award date,
PRIMARY KEY (id)
);
create index detail_name_index on detail (name);
INSERT INTO detail (name, award) values ('Cameron Diaz', '2009-01-01');
INSERT INTO detail (name, award) values ('Cameron Diaz', '2009-02-01');
INSERT INTO detail (name, award) values ('Cameron Diaz', '2009-03-01');
INSERT INTO detail (name, award) values ('Angelina Jolie', '2009-01-01');
INSERT INTO detail (name, award) values ('Angelina Jolie', '2009-02-01');
INSERT INTO detail (name, award) values ('Angelina Jolie', '2009-03-01');
INSERT INTO detail (name, award) values ('Angelina Jolie', '2009-04-01');
INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-01-01');
INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-02-01');
INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-03-01');
INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-04-01');
INSERT INTO detail (name, award) values ('Reese Witherspoon', '2009-05-01');
stium=# explain select * from celeb left join detail on detail.name = celeb.name;
QUERY PLAN
-----------------------------------------------------------------------
Hash Left Join (cost=11.75..25.30 rows=140 width=1044)
Hash Cond: (("outer".name)::text = ("inner".name)::text)
-> Seq Scan on celeb (cost=0.00..11.40 rows=140 width=520)
-> Hash (cost=11.40..11.40 rows=140 width=524)
-> Seq Scan on detail (cost=0.00..11.40 rows=140 width=524)
(5 rows)
Now, why does it use sequential scans?? I don't get it. It also does this
on a very large table which is ANALYZE'd.
Thanks,
Ron
--
NeoNova BV
innovatieve internetoplossingen
http://www.neonova.nl Science Park 140 1098 XG Amsterdam
info: 020-5611300 servicedesk: 020-5611302 fax: 020-5611301
KvK Amsterdam 34151241
Op dit bericht is de volgende disclaimer van toepassing:
http://www.neonova.nl/maildisclaimer
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2009-09-18 11:02:10 | Re: Index not used in join.. (example included) |
Previous Message | Michael Wood | 2009-09-18 06:55:00 | Re: shp2pgsql in java |