Skip site navigation (1) Skip section navigation (2)

Index not used in join.. (example included)

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 (view raw or flat)
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

Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2009-09-18 11:02:10
Subject: Re: Index not used in join.. (example included)
Previous:From: Michael WoodDate: 2009-09-18 06:55:00
Subject: Re: shp2pgsql in java

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group